How to save each tab as a new workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a wookbook with about 30 tabs (worksheets). Is there a way I can
automatically save each worksheet in the workbook as a new workbook, saving
as the tab name.xls? Thanks for your help!
 
Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & w.Name
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
Thanks for the feedback.

Note: you can always change the path to another folder if you wished.

ActiveWorkbook.SaveAs FileName:="C:\myfolder" & "\" & w.Name


Gord
 
Gord,

Again, thank you so much for your help. I am wondering if you can help me
take this another step.

I have a worksheet with multiple columns. After the data has been sorted
(say by customer), is there a macro that can be run that would automatically
save a sheet named for each customer and have just the rows for that
customer, saving as many sheets as there are unique customer names?
 
Gord,

Thanks again for all of your help. This was right on target and was
extremely helpful.
 
Back
Top