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

[JapanDave]

Tr this,

Make sure you change the filepath in the below code to the filepath you want the books to be saved,

Please Login or Register  to view this content.

[freud1]

Thanks JapanDave,

its a great start but I need the macro to copy the whole worksheet including conditional formatting,

also i need it to copy the other two tabs, information1 and information2.

The macro works great for the main function though of looping the data and saving the individual workbooks!

Thanks

F

[JapanDave]

Ok, try this,

[freud1]

Hi Japan Dave,

This works great on my test file but i'm having trouble getting it to apply to my main sheet.

maybe you can help...

could you annotate the code with a little description of what each part is doing?

It will just be a case of changing the references to refer to the real workbook but im having a bit of trouble figuring it out.

many thanks

F

[JapanDave]

Where are getting the error?

[freud1]

Hi Japan Dave,

I don't get a runtime error, i it just runs but does nothing.

I have tried looking at the code and changing the cell references to match the real workbook but i am not having much luck.

I think maybe this could be the problem, the layout on the real workbook is different.

I have attached the worksheet (and removed real data) to show you where on the real workbook the lookup tables are.

Since the entire worksheets are being copied i dont think they would be causing the problems.

Thank you so much for your help with this.

Regards

F

[JapanDave]

You have given me a completely different book. Although the logic is the same the cells and columns that the pertinent values reside are in completely different places. On to of that, there are no tables to copy. I am assuming you took those out due privacy issues. But are they in the exact same range as the sample workbook? I will guess that this code won't work either as those sheets names in the sample book look like they are temporay.

Try the code in this book in a copy of your actual book. And tell where the error occurs(It will)

[freud1]

Hi JapanDave,

Firstly, I apologise for any confusion or messing you around with this.

I can usually ammend VBA code to suit my needs without a problem so in order to simplify things I make a demonstration workbook and then change the code to suit my needs on the real workbook, It seems that in this cas eit has made thigs more complicated!

It was also very difficult to get the file under 1MB to upload but i have done it! please find the file attached.

Lookups contain the tables

Tabs to be copied across to another workbook:
ByManager
ByManagerOOD
MandatoryTraining
CoreTraining
UpcomingCourses

Once again I am very sorry if I wasted any of your time, i am always greatful for help I get on this forum!

Thank you again

F

[freud1]

Hi Guys,

I have worked out the following code:

Please Login or Register  to view this content.

This will do the job of creating the new workbook for an individual record.

All i need now is to add some coding which will run the code above, then add 1 to the value in cell FR8 on the Lookups tab, then run the code again.

Any help? i'm so close!

F