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.

JoeSpareBedroom
Fri May 09 08:47:26 CDT 2008

"Nih" <Nih@discussions.microsoft.com> wrote in message
news:96749E47-BC08-42F9-A402-899AC09FB3B0@microsoft.com...
>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
Fri May 09 12:06:39 CDT 2008

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 <Nih@discussions.microsoft.com> 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.

Ron
Fri May 09 14:13:23 CDT 2008

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" <Nih@discussions.microsoft.com> wrote in message news:96749E47-BC08-42F9-A402-899AC09FB3B0@microsoft.com...
>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.