Macro which delays for processing time

[freud1]

Hi Guys,

Once again I bow before the Gurus of Excel for help.

Please see workbook attached:

TAB: Data - contains raw data
TAB: Report contains the report
TAB: Lookup table contains the lookup values
TABS: Information1 & 2 contain information

I need a macro which will do the following:

1. Lookup the first value in the lookuptable by changing the value in cell B18 to 1.
2. WAIT for calculating to complete (this is just a demo workbook but the real one is much larger and takes a while to calculate once changes have been made)
3. Copy Report tab, Information 1 tab and Information 2 tab onto a new workbook. (pasting values only and not formula, also pasting formatting including conditional formatting)
4. Save the new workbook in a set location as the Manager name in cell B6 on the Report Tab. (eg: Manager6.xls)
5. Repeat the process for each value in the Lookup table.

I need it to complete the calculation process before the macro attempts to copy the data across so I can ensure the data is correctly updated on the new workbooks.

Any help would be greatly appreciated!

Regards

F