Save worksheets to different files

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

Guest

I have a workbook with individual worksheets. I need to save each worksheet
into a different file name so that they can be distributed to different
people. Each file represents the results for one indivual. I have to send
the worksheets to the propoer manager. Is there a way to
automatically/easily save these as seperate excel files?

thanks
 
Tere

Sub Make_New_Books()
Dim w As Worksheet
Application.Calculation = xlCalculationManual
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
Application.Calculation = xlCalculationAutomatic
End Sub


Gord Dibben Excel MVP
 
Back
Top