save each sheet of a workbook as its own workbook

[Nih]

I have a spreadsheet with a list of reports for each department of the office
I work in, each department's list is on its own tab. Is there an easy way to
export this workbook so that each sheet is its own seperate workbook? this
way I can email a department only their list without them getting every other
department's info? I don't want to have to copy each sheet into a blank
workbook and save it that way.

 

[JoeSpareBedroom]

I have a spreadsheet with a list of reports for each department of the
office
I work in, each department's list is on its own tab. Is there an easy way
to
export this workbook so that each sheet is its own seperate workbook?
this
way I can email a department only their list without them getting every
other
department's info? I don't want to have to copy each sheet into a blank
workbook and save it that way.

Right click each sheet's tab, then click "move or copy". In "To book",
choose "new book".

 

[Gord Dibben]

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

 

[Ron de Bruin]

See also this macro for Excel 97-2007
http://www.rondebruin.nl/copy6.htm