"Pausing" a macro for recalculation

[Richard H Knoff]

I've posted this in the .misc group, but was advised to try my luck
here.

I have a workbook containing several sheets. In one of them there's
a list of organizational units. I've written a macro that grabs
selected unit identifiers from the sheet "F Units", copies them one
by one to a cell called Filter, and prints sheets with diagrams and
statistics for each of the units.

The procedure sometimes partly fails, as the filter isn't applied
to the statistics sheet. I guess this happens because the sheets
are printed before Excel finishes recalculating. If this is true, I
believe I can prevent the error by adding a "Wait" command, halting
the printing process for something like 3 seconds.

How can I do this? (Part of) the macro appears below.

---

Sub Print_selected_C_and_D()

For Each cell_in_loop In Selection
Set TargetSheet = Sheets("C Diagram")
TargetSheet.Range("Filter").Value = ActiveCell.Value
Sheets(Array("C Diagram", "D Statistics")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("F Units").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Next

 

[David Adamson]

Two ways I have used after being told about them from kind people on the
newsgroup



For whole seconds delay use "wait"



Application.Wait Now() + TimeValue("00:00:03")



'Put it before the Next Command







If you need half a second then put the following at the top of the module



Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)



And then the following in your code where you want the pause



Sleep 500

 

[Richard H Knoff]

Thanks, David -
just what I was loking for.

Regards,
Richard