I have several back up files and 5 rate dependent files. The rate dependent files pull the most current rate, I paste it into the next column to calculate costs, which get pushed back out the support files to determine a new rate, which I then paste into the next column and I keep doing this until the rate stops changing. Please don't ask why the circular reference; long story and not pertinent to what I need.
I wrote a macro to do all the refreshing, copying, pasting, and saving, but then I have to run the macro several times until the rate variance is zero.
I am looking for a loop that will continue running my macro until the value in cell K20 = 0.
Details
File Name: Allocation_Flow_for_2012_ICS.xlsm
Tab: Summary
Cell: K20
Value Wanted: 0
Code Excerpt (I removed most of code as it is repetitive):
==========================================================Sub B_Final_Rates_Refresh_2012()
'Backup Files
Windows("2012 G&A Expense.xlsx").Activate
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
Windows("Cost Center Rate Summary 2012 - Todd Review.xlsx").Activate
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
Windows("Cost Center Rate Summary 2012.xlsx").Activate
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
Windows("Cost Centers 2012.xlsx").Activate
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
Windows("Labor to Cost Type for Sch H 2012.xlsx").Activate
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
'Begin Rate Pastes
ActiveWindow.WindowState = xlMinimized
Windows("2012 Other Assessments.xlsx").Activate
ActiveWorkbook.RefreshAll
ActiveWindow.WindowState = xlMaximized
ActiveWorkbook.RefreshAll
Range("M4:M12").Select
Selection.Copy
Range("N4:N12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N18").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
Windows("Allocation_Flow_for_2012_ICS.xlsm").Activate
ActiveWorkbook.RefreshAll
ActiveWindow.WindowState = xlMaximized
Range("A1").Select
ActiveWorkbook.RefreshAll
Here is where I want to check the cell value in K20 on the Summary tab of the Allocation Flow file to see if it is zero. If not I want it to run the following macro, save the file and start all over again from the top.
Application.Run "Allocation_Flow_for_2012_ICS.xlsm!Consolidate_Net_Allocations"
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMaximized
End Sub
I have tried some Do Until Loops but I don't know how to write the condition for if the cell k20 isnt zero to run the macro and loop through again until the value in cell k20 is zero.
Hopefully someone can help.
Thanks.
I wrote a macro to do all the refreshing, copying, pasting, and saving, but then I have to run the macro several times until the rate variance is zero.
I am looking for a loop that will continue running my macro until the value in cell K20 = 0.
Details
File Name: Allocation_Flow_for_2012_ICS.xlsm
Tab: Summary
Cell: K20
Value Wanted: 0
Code Excerpt (I removed most of code as it is repetitive):
==========================================================Sub B_Final_Rates_Refresh_2012()
'Backup Files
Windows("2012 G&A Expense.xlsx").Activate
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
Windows("Cost Center Rate Summary 2012 - Todd Review.xlsx").Activate
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
Windows("Cost Center Rate Summary 2012.xlsx").Activate
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
Windows("Cost Centers 2012.xlsx").Activate
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
Windows("Labor to Cost Type for Sch H 2012.xlsx").Activate
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
'Begin Rate Pastes
ActiveWindow.WindowState = xlMinimized
Windows("2012 Other Assessments.xlsx").Activate
ActiveWorkbook.RefreshAll
ActiveWindow.WindowState = xlMaximized
ActiveWorkbook.RefreshAll
Range("M4:M12").Select
Selection.Copy
Range("N4:N12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N18").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
Windows("Allocation_Flow_for_2012_ICS.xlsm").Activate
ActiveWorkbook.RefreshAll
ActiveWindow.WindowState = xlMaximized
Range("A1").Select
ActiveWorkbook.RefreshAll
Here is where I want to check the cell value in K20 on the Summary tab of the Allocation Flow file to see if it is zero. If not I want it to run the following macro, save the file and start all over again from the top.
Application.Run "Allocation_Flow_for_2012_ICS.xlsm!Consolidate_Net_Allocations"
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMaximized
End Sub
I have tried some Do Until Loops but I don't know how to write the condition for if the cell k20 isnt zero to run the macro and loop through again until the value in cell k20 is zero.
Hopefully someone can help.
Thanks.