Choppy Getting Data From Other Workbook 1

[campbemr]

The length of this post is kind of ridiculous, but I have reached the extent of my programming know-how and could use some expert advice.

I have one workbook that is pulling data from 12 other workbooks. On the workbook that pulls data from other workbook I have a shap that is assigned the macro that follows. It is kind of sloppy because I'm not that great at programming with VBA and I have trouble with the Activesheet object. Anyway, right now this code minimizes one file while maximizing the file that it is opening to get the data. This makes it very choppy and the macro takes a while to run. Could you give me any advice on how to make this run a little smoother? And if nothing less, if there is any other way to do the large If statement that I have at the beginning of Cycle_Oval. THanks.

Sub Update()

array_location = 0
file = 1
Call Cycle_Ovals(array_location, file)
Workbooks("12.xls&quot.Close
End Sub
Sub Make_Red(Oval)
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Activate
ActiveSheet.Shapes(Oval).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
End Sub
Sub Make_Green(Oval)
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Activate
ActiveSheet.Shapes(Oval).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
End Sub
Sub Make_White(Oval)
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Activate
ActiveSheet.Shapes(Oval).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid

End Sub

Sub Cycle_Ovals(array_location, file)
For i = 123 To 206
If i = 123 Then
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(1, 1) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
ElseIf i = 130 Then
Workbooks(last_file & ".xls&quot.Close
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(9, 1) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
ElseIf i = 137 Then
Workbooks(last_file & ".xls&quot.Close
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(17, 1) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
ElseIf i = 144 Then
Workbooks(last_file & ".xls&quot.Close
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(25, 1) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
ElseIf i = 151 Then
Workbooks(last_file & ".xls&quot.Close
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(33, 1) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
ElseIf i = 158 Then
Workbooks(last_file & ".xls&quot.Close
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(41, 1) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
ElseIf i = 165 Then
Workbooks(last_file & ".xls&quot.Close
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(1, 13) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
ElseIf i = 172 Then
Workbooks(last_file & ".xls&quot.Close
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(9, 13) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
ElseIf i = 179 Then
Workbooks(last_file & ".xls&quot.Close
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(17, 13) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
ElseIf i = 186 Then
Workbooks(last_file & ".xls&quot.Close
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(25, 13) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
ElseIf i = 193 Then
Workbooks(last_file & ".xls&quot.Close
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(33, 13) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
ElseIf i = 200 Then
Workbooks(last_file & ".xls&quot.Close
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(41, 13) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
End If


Cell_Locations = Array(67, 7, 16, 28, 52, 64, 66)
j = Cell_Locations(array_location)
If Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value = "" Then
Call Make_White("Oval " & i)
ElseIf Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(j, 9).Value > Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(j, 8).Value Then
Call Make_Red("Oval " & i)
Else
Call Make_Green("Oval " & i)
End If
array_location = array_location + 1
If array_location = 7 Then
array_location = 0
file = file + 1
last_file = file - 1
End If
Next
End Sub

 

[kylua]

Stick a great big form with a nice picture up to hide what's going on behind. Set the form.showmodal to false otherwise your code will stop when it's shown.

as for the if statement:
'handle 123 as it's different
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(1, 1) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value

'and for the rest, (all identical apart from row?)
for mycol = 1 to 13 step 13
for myrow = 1 to 41 step 8
Workbooks(last_file & ".xls&quot.Close
Workbooks.Open file & ".xls"
Workbooks("Status.xls&quot.Worksheets("Cell Status&quot.Cells(myrow, mycol) = Workbooks(file & ".xls&quot.Worksheets("Signoff Sheet&quot.Cells(1, 1).Value
next myrow
next mycol

Should do it but I have absolutely no comprehension of why you've looped i beween 123 & 200 so I may have missed something.

 

[DaleWatson123321]

campbemr,

The best line of code to use to eliminate the "choppy" effect, and decrease the speed dramatically, is:
Application.ScreenUpdating = False.
Use this at the beginning of your routine.

At the end, use: Application.ScreenUpdating = True

Hope this helps. Please advise as to the results.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Correction... (It's been a LONG day) ...

Where I said "decrease the speed dramatically", I obviously meant to say "INCREASE the speed dramatically".

 

[campbemr]

Thanks for the help. I used Dale's method and it did exactly what I wanted. Thanks