I have code that changes a cell to a new ID which in turn updates several MSQueries and a few Vlookups on multiple sheets. Then I save the sheet, print the sheet and run the process all over again. My code works fine if I am just going through it line by line but it doesn't work if I run it as it doesn't finish the refresh all or the save (where it wants to refresh again) before the next line of code starts.
I have tried to pause the code but that doesn't seem to work either. Any ideas on how to let one line of code finish completely prior to starting the next line.
Code below:
Sub WomensHealthSaveandPrint()
Dim TerrID As String
Dim Counter As Integer
ActiveWorkbook.Worksheets("Variables").Visible = xlVeryHidden
Counter = 28
TerrID = ActiveWorkbook.Worksheets("Variables").Range("A" + CStr(Counter))
Do While Counter <= 50
TerrID = ActiveWorkbook.Worksheets("Variables").Range("A" + CStr(Counter))
ActiveWorkbook.Worksheets("Variables").Range("B1") = TerrID
ActiveWorkbook.refreshall
Counter = Counter + 1
Worksheets("Bricks HRT").Activate
RLC = LastCell(ActiveSheet).Address(False, False)
Worksheets("Bricks HRT").PageSetup.PrintArea = ("A1:" & RLC)
' If LastCol <= 70 Then
' Worksheets("Bricks HRT").PageSetup.Zoom = False
' Worksheets("Bricks HRT").PageSetup.FitToPagesWide = False
' Worksheets("Bricks HRT").PageSetup.FitToPagesTall = 1
' End If
Worksheets("Bricks Contra").Activate
RLC = LastCell(ActiveSheet).Address(False, False)
Worksheets("Bricks Contra").PageSetup.PrintArea = ("A1:" & RLC)
' If LastCol <= 70 Then
' Worksheets("Bricks Contra").PageSetup.Zoom = False
' Worksheets("Bricks Contra").PageSetup.FitToPagesWide = False
' Worksheets("Bricks Contra").PageSetup.FitToPagesTall = 1
'End If
Sheets(Array("HRT Summary", "Bricks HRT", "Contra Summary", "Bricks Contra", "Incentives")).PrintOut
ActiveWorkbook.SaveAs Filename:="c:tempz" & TerrID & "July.xls", FileFormat:=xlWorkbookNormal
Loop
ActiveWorkbook.Close
Workbooks.Open "R:STATIMWomen's Health Statim Build.xls"
ActiveWorkbook.Worksheets("Variables").Visible = True
End Sub
I have tried to pause the code but that doesn't seem to work either. Any ideas on how to let one line of code finish completely prior to starting the next line.
Code below:
Sub WomensHealthSaveandPrint()
Dim TerrID As String
Dim Counter As Integer
ActiveWorkbook.Worksheets("Variables").Visible = xlVeryHidden
Counter = 28
TerrID = ActiveWorkbook.Worksheets("Variables").Range("A" + CStr(Counter))
Do While Counter <= 50
TerrID = ActiveWorkbook.Worksheets("Variables").Range("A" + CStr(Counter))
ActiveWorkbook.Worksheets("Variables").Range("B1") = TerrID
ActiveWorkbook.refreshall
Counter = Counter + 1
Worksheets("Bricks HRT").Activate
RLC = LastCell(ActiveSheet).Address(False, False)
Worksheets("Bricks HRT").PageSetup.PrintArea = ("A1:" & RLC)
' If LastCol <= 70 Then
' Worksheets("Bricks HRT").PageSetup.Zoom = False
' Worksheets("Bricks HRT").PageSetup.FitToPagesWide = False
' Worksheets("Bricks HRT").PageSetup.FitToPagesTall = 1
' End If
Worksheets("Bricks Contra").Activate
RLC = LastCell(ActiveSheet).Address(False, False)
Worksheets("Bricks Contra").PageSetup.PrintArea = ("A1:" & RLC)
' If LastCol <= 70 Then
' Worksheets("Bricks Contra").PageSetup.Zoom = False
' Worksheets("Bricks Contra").PageSetup.FitToPagesWide = False
' Worksheets("Bricks Contra").PageSetup.FitToPagesTall = 1
'End If
Sheets(Array("HRT Summary", "Bricks HRT", "Contra Summary", "Bricks Contra", "Incentives")).PrintOut
ActiveWorkbook.SaveAs Filename:="c:tempz" & TerrID & "July.xls", FileFormat:=xlWorkbookNormal
Loop
ActiveWorkbook.Close
Workbooks.Open "R:STATIMWomen's Health Statim Build.xls"
ActiveWorkbook.Worksheets("Variables").Visible = True
End Sub