save each sheet of a workbook as its own workbook
 

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.

save each sheet of a workbook as its own workbook
 

"Nih" wrote in message
...
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".

save each sheet of a workbook as its own workbook
 

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


On Fri, 9 May 2008 05:59:01 -0700, Nih wrote:

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.

save each sheet of a workbook as its own workbook
 

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Nih" wrote in message ...
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.