Macro that prints after recalc is finished

[valglad]

Hi,

I've got a relatively large spreadsheet (25 MB) with a lot of formulas
(some of them are UDFs). Most of the calculation is done in a sheet
called "data", the summary is presented in sheet "Report", which only
has about a couple of dozens of Sumifs but that's about it.

The purpose of that file is to allow the user to key in an order number
value in cell B5 after which all the recalcs kick in. It usually takes
about 15 secs to recalc, sometimes might take longer or shorter.

Now one of the users asked me to print summaries for all order numbers,
which is about a thousand. I can't imagine doing it manually, so I've
got to write a macro to do it. I can see looping through the order
numbers and printing the summary page for each one.

However, I don't know how to ensure that the summary page is printed
only after all recalculations are finished, otherwise it might print a
bunch of summaries with partially calculated formulas and then I'll get
crusified.

Could anyone give me some clue as to how to achieve this.

Thanks in advance.

I work with Excel XP

 

[Dave Peterson]

Do you have a list of all the order numbers somewhere?

If you do, you could loop through that list, do a recalc, print, and go to the
next order number.

I put my list in A2:A### of sheet1. I only printed Sheet2.

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myOrderRng As Range

With Worksheets("sheet1")
Set myOrderRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myOrderRng.Cells
Worksheets("sheet2").Range("B5").Value = myCell.Value
Application.Calculate
Worksheets("sheet2").PrintOut preview:=True
Next myCell

End Sub

In fact, I did a print preview (for testing).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm