Save worksheets to different files

[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

 

[Ron de Bruin]

Hi Tere

Here is a example
http://www.rondebruin.nl/copy6.htm

If you want to also mail the workbooks then look here
http://www.rondebruin.nl/mail/folder1/mail5.htm
Or
http://www.rondebruin.nl/mail/folder2/mail5.htm

 

[Guest]

Thanks Ron. This solved my issue.

Hi Tere

Here is a example
http://www.rondebruin.nl/copy6.htm

If you want to also mail the workbooks then look here
http://www.rondebruin.nl/mail/folder1/mail5.htm
Or
http://www.rondebruin.nl/mail/folder2/mail5.htm

 

[Gord Dibben]

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