Code running too fast - how to pause code to allow save and

bentleyr

New Member
Joined
Jul 29, 2002
Messages
17
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
bentleyr,

Any ideas on how to let one line of code finish completely prior to starting the next line.

Following code will hopefully get You started:

<pre>
Option Explicit

Sub NumberOfSecunds()
Dim lnSeconds As Long
lnSeconds = 5
Call PauseMacro(lnSeconds)
End Sub

Sub PauseMacro(lnSeconds As Long)
Dim lnTime As Long
lnTime = Time

Do Until Time >= lnTime + (1 / 86400 * lnSeconds)
If Time < lnTime Then Exit Do
Loop

MsgBox "5 seconds break..."
End Sub
</pre>

HTH,
Dennis
 
Upvote 0
Bentlyr,

What I do to allow the queries result to take affect is
Sheets(2).QueryTables("PREMIUMS").BackgroundQuery = False
Sheets(2).QueryTables("PREMIUMS").Refresh
After that I would then calculate the workbook so all formulas connected are updated.

Hope this helps!

Steve
 
Upvote 0

Forum statistics

Threads
1,218,620
Messages
6,143,518
Members
450,492
Latest member
Rusbus1972

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top