Solved: Finish Data update before rest of macro runs

[MDY]

Hi,
I'm just wondering how I can have a section of my code complete before the next section starts. In the code I have a section that has an external data refresh Section to it but the problem is that the rest of my code is running before the actual data refresh is complete. Is there any way to get the code to pause until the refresh is complete and then continue with what needs to be done? My code is as below, I know it could be written far better but it works for what it needs to do although slow, if you would like to tidy it up please feel free. I know I can just break it into two seperate macros but would prefer to leave it running as one.

Thanks for your assistance.

MDY

[VBA]Sub MPMDataRefresh()

Rows("3:3").Select
Selection.AutoFilter
Cells.Select
Range("BH1").Activate
Selection.Copy
Sheets("MPM Data Copy").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("MPM Database Data").Select
Range("CA4").Select
Application.CutCopyMode = False
'
ActiveWorkbook.RefreshAll
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 60

'Code needs to be able to pause or finsih what is happening above in the data refresh before this next bit starts

ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,79,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,79,FALSE)))"
Range("CB4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,80,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,80,FALSE)))"
Range("CD4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,82,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,82,FALSE)))"
Range("CE4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,83,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,83,FALSE)))"
Range("CE5").Select
ActiveWindow.SmallScroll ToRight:=5
Range("CG4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,85,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,85,FALSE)))"
Range("CJ4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,88,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,88,FALSE)))"
Range("CK4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,89,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,89,FALSE)))"
Range("CK11").Select
ActiveWindow.SmallScroll ToRight:=-6
Range("CA4:CB4").Select
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 482
ActiveWindow.ScrollRow = 842
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 1443
ActiveWindow.ScrollRow = 2044
ActiveWindow.ScrollRow = 3005
ActiveWindow.ScrollRow = 4087
ActiveWindow.ScrollRow = 5769
ActiveWindow.ScrollRow = 6610
ActiveWindow.ScrollRow = 6130
ActiveWindow.ScrollRow = 5409
ActiveWindow.ScrollRow = 4928
ActiveWindow.SmallScroll Down:=42
Range("CA4:CB5000").Select
Selection.FillDown
ActiveWindow.ScrollRow = 3846
ActiveWindow.ScrollRow = 2044
ActiveWindow.ScrollRow = 602
ActiveWindow.ScrollRow = 1
Range("CD4").Select
ActiveWindow.ScrollRow = 602
ActiveWindow.ScrollRow = 2164
ActiveWindow.ScrollRow = 3967
ActiveWindow.ScrollRow = 4688
ActiveWindow.ScrollRow = 5288
ActiveWindow.ScrollRow = 5889
ActiveWindow.ScrollRow = 6971
ActiveWindow.ScrollRow = 5769
ActiveWindow.ScrollRow = 5649
ActiveWindow.ScrollRow = 5168
ActiveWindow.ScrollRow = 4567
ActiveWindow.ScrollRow = 4087
ActiveWindow.ScrollRow = 4327
ActiveWindow.ScrollRow = 4447
ActiveWindow.ScrollRow = 4688
ActiveWindow.ScrollRow = 5168
ActiveWindow.SmallScroll Down:=-183
Range("CD4:CE5000").Select
Selection.FillDown
ActiveWindow.SmallScroll ToRight:=3
ActiveWindow.ScrollRow = 3366
ActiveWindow.ScrollRow = 2044
ActiveWindow.ScrollRow = 842
ActiveWindow.ScrollRow = 1
Range("CG4").Select
ActiveWindow.ScrollRow = 722
ActiveWindow.ScrollRow = 962
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 1563
ActiveWindow.ScrollRow = 2164
ActiveWindow.ScrollRow = 3005
ActiveWindow.ScrollRow = 3366
ActiveWindow.ScrollRow = 3486
ActiveWindow.ScrollRow = 4688
ActiveWindow.ScrollRow = 5649
ActiveWindow.ScrollRow = 6851
ActiveWindow.ScrollRow = 5649
ActiveWindow.ScrollRow = 5529
ActiveWindow.ScrollRow = 5048
ActiveWindow.ScrollRow = 4928
ActiveWindow.SmallScroll Down:=51
Range("CG4:CG5000").Select
Selection.FillDown
ActiveWindow.SmallScroll ToRight:=3
ActiveWindow.ScrollRow = 3125
ActiveWindow.ScrollRow = 2404
ActiveWindow.ScrollRow = 1443
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 962
ActiveWindow.ScrollRow = 722
ActiveWindow.ScrollRow = 602
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll Down:=-6
Range("CJ4:CK4").Select
ActiveWindow.ScrollRow = 1083
ActiveWindow.ScrollRow = 2404
ActiveWindow.ScrollRow = 4808
ActiveWindow.ScrollRow = 5048
ActiveWindow.ScrollRow = 5529
ActiveWindow.ScrollRow = 6610
ActiveWindow.ScrollRow = 7812
ActiveWindow.ScrollRow = 8773
ActiveWindow.ScrollRow = 8893
ActiveWindow.ScrollRow = 7932
ActiveWindow.ScrollRow = 7812
ActiveWindow.ScrollRow = 7451
ActiveWindow.ScrollRow = 7211
ActiveWindow.ScrollRow = 6610
ActiveWindow.SmallScroll Down:=-39
ActiveWindow.ScrollRow = 6250
ActiveWindow.ScrollRow = 6130
ActiveWindow.ScrollRow = 5889
ActiveWindow.ScrollRow = 5769
ActiveWindow.ScrollRow = 5409
ActiveWindow.ScrollRow = 5288
ActiveWindow.ScrollRow = 5168
ActiveWindow.ScrollRow = 5048
ActiveWindow.ScrollRow = 4928
ActiveWindow.ScrollRow = 4808
ActiveWindow.SmallScroll Down:=162
Range("CJ4:CK5000").Select
Selection.FillDown
ActiveWindow.ScrollRow = 4447
ActiveWindow.ScrollRow = 4327
ActiveWindow.ScrollRow = 3967
ActiveWindow.ScrollRow = 3606
ActiveWindow.ScrollRow = 3486
ActiveWindow.ScrollRow = 3246
ActiveWindow.ScrollRow = 3125
ActiveWindow.ScrollRow = 2885
ActiveWindow.ScrollRow = 2404
ActiveWindow.ScrollRow = 1804
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 602
ActiveWindow.ScrollRow = 362
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 962
ActiveWindow.ScrollRow = 2525
ActiveWindow.ScrollRow = 3125
ActiveWindow.ScrollRow = 3486
ActiveWindow.ScrollRow = 3846
ActiveWindow.ScrollRow = 4087
ActiveWindow.ScrollRow = 4447
ActiveWindow.ScrollRow = 4567
ActiveWindow.ScrollRow = 4928
ActiveWindow.ScrollRow = 5048
ActiveWindow.ScrollRow = 5409
ActiveWindow.ScrollRow = 5769
ActiveWindow.ScrollRow = 5889
ActiveWindow.ScrollRow = 4808
ActiveWindow.ScrollRow = 4688
ActiveWindow.ScrollRow = 4567
ActiveWindow.ScrollRow = 3366
ActiveWindow.ScrollRow = 2404
ActiveWindow.ScrollRow = 1683
ActiveWindow.ScrollRow = 962
ActiveWindow.ScrollRow = 482
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll ToRight:=-6
Range("CA4").Select
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 482
ActiveWindow.ScrollRow = 722
ActiveWindow.ScrollRow = 842
ActiveWindow.ScrollRow = 1083
ActiveWindow.ScrollRow = 1323
ActiveWindow.ScrollRow = 1563
ActiveWindow.ScrollRow = 1804
ActiveWindow.ScrollRow = 2044
ActiveWindow.ScrollRow = 3005
ActiveWindow.ScrollRow = 3486
ActiveWindow.ScrollRow = 3606
ActiveWindow.ScrollRow = 4207
ActiveWindow.ScrollRow = 4327
ActiveWindow.ScrollRow = 4447
ActiveWindow.ScrollRow = 4688
ActiveWindow.ScrollRow = 4808
ActiveWindow.ScrollRow = 5048
ActiveWindow.ScrollRow = 5409
ActiveWindow.ScrollRow = 5529
ActiveWindow.ScrollRow = 5649
ActiveWindow.ScrollRow = 5048
ActiveWindow.ScrollRow = 4928
ActiveWindow.ScrollRow = 4567
ActiveWindow.ScrollRow = 3846
ActiveWindow.ScrollRow = 4567
ActiveWindow.ScrollRow = 4808
ActiveWindow.ScrollRow = 5048
ActiveWindow.SmallScroll Down:=-60
Range("CA4:CB5000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("CD4").Select
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 602
ActiveWindow.ScrollRow = 1323
ActiveWindow.ScrollRow = 2164
ActiveWindow.ScrollRow = 4567
ActiveWindow.ScrollRow = 5288
ActiveWindow.ScrollRow = 6250
ActiveWindow.ScrollRow = 6730
ActiveWindow.ScrollRow = 7451
ActiveWindow.ScrollRow = 7572
ActiveWindow.ScrollRow = 6971
ActiveWindow.ScrollRow = 6130
ActiveWindow.ScrollRow = 5649
ActiveWindow.ScrollRow = 5529
ActiveWindow.ScrollRow = 5168
ActiveWindow.ScrollRow = 4928
ActiveWindow.ScrollRow = 4808
ActiveWindow.ScrollRow = 4567
ActiveWindow.ScrollRow = 5769
ActiveWindow.SmallScroll Down:=-102
ActiveWindow.LargeScroll Down:=-16
Range("CD4:CE5000").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=3
Range("CG4").Select
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 362
ActiveWindow.ScrollRow = 482
ActiveWindow.ScrollRow = 722
ActiveWindow.ScrollRow = 842
ActiveWindow.ScrollRow = 962
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 1323
ActiveWindow.ScrollRow = 1563
ActiveWindow.ScrollRow = 1683
ActiveWindow.ScrollRow = 2164
ActiveWindow.ScrollRow = 2404
ActiveWindow.ScrollRow = 2645
ActiveWindow.ScrollRow = 2885
ActiveWindow.ScrollRow = 3125
ActiveWindow.ScrollRow = 3606
ActiveWindow.ScrollRow = 3967
ActiveWindow.ScrollRow = 4447
ActiveWindow.ScrollRow = 4567
ActiveWindow.ScrollRow = 5048
ActiveWindow.ScrollRow = 5168
ActiveWindow.ScrollRow = 5529
ActiveWindow.ScrollRow = 5168
ActiveWindow.ScrollRow = 5048
ActiveWindow.SmallScroll Down:=-57
Range("CG4:CG5000").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=3
Range("CJ4").Select
ActiveWindow.ScrollRow = 1443
ActiveWindow.ScrollRow = 2284
ActiveWindow.ScrollRow = 3366
ActiveWindow.ScrollRow = 4327
ActiveWindow.ScrollRow = 5048
ActiveWindow.ScrollRow = 5409
ActiveWindow.ScrollRow = 5889
ActiveWindow.ScrollRow = 5168
ActiveWindow.ScrollRow = 5048
ActiveWindow.ScrollRow = 4567
ActiveWindow.SmallScroll Down:=225
ActiveWindow.LargeScroll Down:=6
ActiveWindow.SmallScroll Down:=-45
Range("CJ4:CK5000").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-15
Rows("3:3").Select
Range("CD3").Activate
ActiveWindow.ScrollRow = 1443
ActiveWindow.ScrollRow = 2885
ActiveWindow.ScrollRow = 5048
ActiveWindow.ScrollRow = 7451
ActiveWindow.ScrollRow = 9975
ActiveWindow.ScrollRow = 11537
ActiveWindow.ScrollRow = 13220
ActiveWindow.ScrollRow = 14421
ActiveWindow.ScrollRow = 14541
ActiveWindow.ScrollRow = 12138
ActiveWindow.ScrollRow = 10936
ActiveWindow.ScrollRow = 9735
ActiveWindow.ScrollRow = 8893
ActiveWindow.ScrollRow = 8052
ActiveWindow.ScrollRow = 7331
ActiveWindow.ScrollRow = 6610
ActiveWindow.ScrollRow = 5409
ActiveWindow.ScrollRow = 4928
ActiveWindow.ScrollRow = 4688
ActiveWindow.ScrollRow = 4447
ActiveWindow.ScrollRow = 4087
ActiveWindow.ScrollRow = 3846
ActiveWindow.ScrollRow = 3606
ActiveWindow.ScrollRow = 3125
ActiveWindow.ScrollRow = 2885
ActiveWindow.ScrollRow = 3846
ActiveWindow.ScrollRow = 4567
ActiveWindow.ScrollRow = 4928
ActiveWindow.ScrollRow = 5048
ActiveWindow.ScrollRow = 4808
ActiveWindow.SmallScroll Down:=159
Rows("3:5000").Select
Range("CD3").Activate
Application.CutCopyMode = False
Selection.Sort Key1:=Range("H4"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
ActiveWindow.SmallScroll ToRight:=6
Rows("3:3").Select
Range("CE3").Activate
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=21
Range("CL4").Select
Selection.AutoFilter Field:=90, Criteria1:="Current"
ActiveWindow.SmallScroll ToRight:=-18
End Sub[/VBA]

[Kenneth Hobs]

Try:
[VBA]DoEvents[/VBA]

[MDY]

Hi Kenneth,
Sorry but im still very much a novice at all this. Would you mind giving me an example of how DoEvents works?

thanks
Mdy

[MDY]

Hi,
Ive had this problem for a while and I really need a hand with it. Is it possible to get one macro finish completely i.e. data refreshing, before the next part of the macro begins?

Keneth suggested DoEvents, Can anyone explain how this might work for the above macros?

Thanks
MDY

[jwise]

I think you must add the "DoEvents" to each unit of your code. Unit means several related statements and the ensuing "copy" or whatever. For example, you may select a "from" range, a "destination" range, and then the actual copy. All of this would be a "unit". After this unit, you would add the "DoEvents". Thus each unit of work would complete before the next unit would begin.

[MDY]

Hi Jwise,
Thanks heaps for the reply. Do you have any examples of how to combine 2 macros using DoEvents?

Your help is much appreciated.

Thanks
Mdy

[MDY]

Hi All,
Please note that the solution to this thread is continued in:

http://www.vbaexpress.com/forum/showthread.php?t=30743

Thanks
Mdy