Worksheet saved out as new workbook

[Guest]

I have a workbook which contains about 15 worksheets. I update the data for
the workbook and now need to save the individual sheets as separate workbooks
using the sheet names as the file name.

How do I loop through each worksheet and save them as separate workbooks
using the sheet 'tab' names as the file names?

Sub SaveSheet()

ActiveSheet.Copy
ActiveWorkbook.SaveAs "C:\Data\AMReports\" & ActiveSheet.Name & ".xls"
ActiveWorkbook.Close SaveChanges:=False

End Sub

Thanks - -TB- -

 

[Colby Africa]

Something like this:

Dim worksheetItem As Worksheet
Dim newWorkBookItem As Workbook

For Each worksheetItem In ThisWorkbook.Worksheets

Set newWorkBookItem = Application.Workbooks.Add

worksheetItem.Copy newWorkBookItem.Sheets(1)

newWorkBookItem.SaveAs "C:\data\temp\" + worksheetItem.Name +
".xls"

Next

You need to clean up the newWorkBookItem because it will now have 4
worksheets (the default 3 created + your new inserted worksheet).

Hope this helps.

Colby Africa
http://colbyafrica.blogspot.com

 

[Ron de Bruin]

See this page tom
http://www.rondebruin.nl/copy6.htm

 

[Guest]

Thanks Ron (and Colby). I used adjusted the code just a bit and it worked as
advertised!

Thanks again
- -TB - -