Copy only a few sheets from workbook

[Aurbo99]

Greetings

I have 16 Sheets in my workbook, I only want to copy 12 of them to a new workbook.

I am creating new workbooks based on the code below but its coping all 16 sheets.

the 12 sheets are named Jan through Dec

Please Login or Register  to view this content.

I have 27 employees identified on Sheet1,A3:A30

and this creates 27 new workbooks for them including the 4 extra sheets I dont want/need them to see.

Would it be easier to create a second "Employee.xls" workbook and have it contain ONLY the 12 sheets? if so, how would I alter this code to allow for that?

I perfer to keep only a single workbook master but either way will work.

Any clues?

[Leith Ross]

Hello Aurbo99,

Add this code to the workbook with the 12 sheets you want to copy. In the code below, add the path to the "Employee.xls" workbook. It is marked in blue. the new workbook must always contain 1 worksheet. Delete all the other sheets and leave "Sheet1" in the Employees workbook.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Aurbo99]

Update in color with answers match color in code.

Still 3 &4

Thanks again for the nudge in the right direction..

I have a few more questions.

The code below is working fine for me. Its amazing to watch it run.. I keep deleting the final product and re-running the macro just to see it run again!

Anyhow..

1. How would I change the code below to loop only the number of times needed for the actual number of employees in column A of Sheet1?

Right now it loops 26 times..

2. How can I check for the existance of the Path and create it if it does not exist? Done!

3. If the path exists, and the macro is running, if it encounters an employee's file already there, give the user the option to over-write the file or skip to the next. Over-write query is automatic, but any input other than YES will break the code, I think I need "ONERR" as well?

4. Is there a way to clean this up and maybe shorten up the code? Arrays? or something else? there seems to still be plenty of repeated steps that may be changed to variables or "Dim" I think..

Please Login or Register  to view this content.

Cheers
Aurbo