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
"Lee" <(E-Mail Removed)> wrote in message
news:2E124DE5-B24E-42FF-9DB8-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:19D630D1-BD16-4FAE-8DEF-(E-Mail Removed)...
>> > 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
>>
>>
>>
|
Similar Threads
