Move each sheet in workbook to new workbook
Posted by Alice on December 06, 2001 7:44 PM
I want to write a macro that will do 3 things without needing to know the names of the sheets:
1. format all sheets the same way
2. move each sheet to new workbook
3. be prompted to save as on each new workbook
Is this possible?
Posted by Colo on December 06, 2001 8:36 PM
It is possible if use "For Each Next statment"
I wonder if my sample code satisfy your need, here's a sample code.
Sub SampleForEach()
Dim Sh As Worksheet
For Each Sh In Worksheets
'Please write here "macro for format"
Sh.Copy
ActiveWorkbook.Close True
Next
End Sub
Posted by Alice on December 07, 2001 4:14 AM
It seems like this will work,but I want to keep the new workbooks open. Changing Close to Save As doesn't seem to be working. Any suggestions? Thanks :-) Adrae
Posted by Colo on December 07, 2001 1:42 PM
Hi
:I want to keep the new workbooks open
So please replace
ActiveWorkbook.Close True
«
ActiveWorkbook.Saveas "fullpath"
Posted by Alice on December 08, 2001 11:30 AM
This misses the last sheet. How can I modify the code to include this sheet? Thanks Again :-)
Alice
Posted by Alice on December 08, 2001 12:02 PM
The formatting is skipped on the last sheet not the copying.
This misses the last sheet. How can I modify the code to include this sheet? Thanks Again :-)
