save each sheet of a workbook as its own workbook

N

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.
 
J

JoeSpareBedroom

Nih said:
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.
Click to expand...


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

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
 
Top