Looping Question

[Lee]

Hello,

I have a workbook that has 50 sheets. I want to create a loop that will
copy each individual sheet to its own workbook and save it with a name.

This is what I have so far. Any help would be great.


Sheets("1").Select
Sheets("1").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\hola\Desktop\Legal Entity\Master LE
Files\WCA Individual Emails\World Class Accounting Responsibility - 1.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Windows("World Class Accounting Email.xls").Activate
Sheets("2").Select
Sheets("2").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\hola\Desktop\Legal Entity\Master LE
Files\WCA Individual Emails\World Class Accounting Responsibility - 2.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

 

[Paul B]

Lee, see if this will get you started

Sub Copy_Sheets_As_New_Workbook()
'will take each sheet in the workbook and save it into their own work book
'by sheet name, so sheet1 will become sheet1.xla, will over write files if
there is one
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
ws.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Name
ActiveWorkbook.Close
Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Lee]

Paul,

Thanks for the code last time, I was wondering for each new workbook I
create I want to call the sheet in the workbook "Master". How do you do
that. I am not sure what I have is correct. Thanks

Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
ws.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "World
Class Accounting Responsibility " & ws.Name
ws.Select
ws.Name = "Master"
ActiveWorkbook.Close
Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True

 

[Paul B]

Lee, try this,

Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
ws.Copy
ActiveSheet.Name = "Master"

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & _
"World Class Accounting Responsibility " & ws.Name


ActiveWorkbook.Close
Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003