MDY
02-23-2010, 07:37 PM
Hi All,
I've searched through threads and experimented with code trying to solve this one but havn't ahd any luck at this stage.
Im trying to get the second part of my Macro to run once
ActiveWorkbook.RefreshAll
Is complete and only once it is complete without using a timer but on event finish. I had posted a similar scenario in another post:
http://www.vbaexpress.com/forum/showthread.php?t=25315
But probably wasn't specific enough.
User Keneth Hobs suggested DoEvents and Jwise tried to help me out with this but I was still unable to get this to work.
My code is as follows:
Sub MPMDataRefresh()
'
'
'Select MPM Database Data Sheet
Sheets("MPM Database Data").Select
'Turn Off Auto Filter
Rows("3:3").Select
Selection.AutoFilter
'Copy Data in Sheet MPM Database data
Cells.Select
Range("BH1").Activate
Selection.Copy
'Paste Data into Sheet
Sheets("MPM Data Copy").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Refresh Data in Workbook
Sheets("MPM Database Data").Select
Range("CB4").Select
Application.CutCopyMode = False
ActiveWorkbook.RefreshAll
'Enter Section here to ensure ActiveWorkbook.RefreshAll Finishes before rest of macro runs Possibly - DoEvents
'Enter Section here to ensure ActiveWorkbook.RefreshAll Finishes before rest of macro runs Possibly - DoEvents
'Enter Section here to ensure ActiveWorkbook.RefreshAll Finishes before rest of macro runs Possibly - DoEvents
'Delete Calculate values at top of sheet so that vlookup works properly
Sheets("MPM Data Copy").Select
Range("A1").Select
Selection.ClearContents
Range("A2").Select
Selection.ClearContents
'Select MPM Database Data for rest of formula
Sheets("MPM Database Data").Select
'Insert Vlookup formula into Cells to Copy data from MPM Data Copy
Range("AC4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,29,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,29,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("CC4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,81,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,81,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("CF4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,84,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,84,FALSE)))"
Range("CH4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,86,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,86,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("CL4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,90,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,90,FALSE)))"
'Fill down the Formulas as Above to the Spreadsheet
Range("AC4:AC5000").Select
Selection.FillDown
Range("CB4:CC5000").Select
Selection.FillDown
Range("CE4:CF5000").Select
Selection.FillDown
Range("CH4:CH5000").Select
Selection.FillDown
Range("CK4:CL5000").Select
Selection.FillDown
'Copy and Paste the Cells Section of Macro
Range("AC4:AC5000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("CB4:CC5000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("CE4:CF5000").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=3
Range("CH4:CH5000").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("CK4:CL5000").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Sort Records by Project Ranking
'Selct Rows to Sort
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
'Filter Records by Current Records
Rows("3:3").Select
Range("CE3").Activate
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=21
Range("CM4").Select
Selection.AutoFilter Field:=91, Criteria1:="Current"
ActiveWindow.SmallScroll ToRight:=-18
End Sub
It would be awesome if someone could give me a hand on this one.
Thanks
MDY
I've searched through threads and experimented with code trying to solve this one but havn't ahd any luck at this stage.
Im trying to get the second part of my Macro to run once
ActiveWorkbook.RefreshAll
Is complete and only once it is complete without using a timer but on event finish. I had posted a similar scenario in another post:
http://www.vbaexpress.com/forum/showthread.php?t=25315
But probably wasn't specific enough.
User Keneth Hobs suggested DoEvents and Jwise tried to help me out with this but I was still unable to get this to work.
My code is as follows:
Sub MPMDataRefresh()
'
'
'Select MPM Database Data Sheet
Sheets("MPM Database Data").Select
'Turn Off Auto Filter
Rows("3:3").Select
Selection.AutoFilter
'Copy Data in Sheet MPM Database data
Cells.Select
Range("BH1").Activate
Selection.Copy
'Paste Data into Sheet
Sheets("MPM Data Copy").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Refresh Data in Workbook
Sheets("MPM Database Data").Select
Range("CB4").Select
Application.CutCopyMode = False
ActiveWorkbook.RefreshAll
'Enter Section here to ensure ActiveWorkbook.RefreshAll Finishes before rest of macro runs Possibly - DoEvents
'Enter Section here to ensure ActiveWorkbook.RefreshAll Finishes before rest of macro runs Possibly - DoEvents
'Enter Section here to ensure ActiveWorkbook.RefreshAll Finishes before rest of macro runs Possibly - DoEvents
'Delete Calculate values at top of sheet so that vlookup works properly
Sheets("MPM Data Copy").Select
Range("A1").Select
Selection.ClearContents
Range("A2").Select
Selection.ClearContents
'Select MPM Database Data for rest of formula
Sheets("MPM Database Data").Select
'Insert Vlookup formula into Cells to Copy data from MPM Data Copy
Range("AC4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,29,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,29,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("CC4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,81,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,81,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("CF4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,84,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,84,FALSE)))"
Range("CH4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,86,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,86,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("CL4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1="""","""",IF(VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,90,FALSE)="""","""",VLOOKUP(RC1,'MPM Data Copy'!R1:R65536,90,FALSE)))"
'Fill down the Formulas as Above to the Spreadsheet
Range("AC4:AC5000").Select
Selection.FillDown
Range("CB4:CC5000").Select
Selection.FillDown
Range("CE4:CF5000").Select
Selection.FillDown
Range("CH4:CH5000").Select
Selection.FillDown
Range("CK4:CL5000").Select
Selection.FillDown
'Copy and Paste the Cells Section of Macro
Range("AC4:AC5000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("CB4:CC5000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("CE4:CF5000").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=3
Range("CH4:CH5000").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("CK4:CL5000").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Sort Records by Project Ranking
'Selct Rows to Sort
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
'Filter Records by Current Records
Rows("3:3").Select
Range("CE3").Activate
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=21
Range("CM4").Select
Selection.AutoFilter Field:=91, Criteria1:="Current"
ActiveWindow.SmallScroll ToRight:=-18
End Sub
It would be awesome if someone could give me a hand on this one.
Thanks
MDY