I have a workbook with several worksheets (30) and am trying to find a way to
save each worksheet in the book as a new excel file using the name of the
worksheet as the new file name...I use the move or copy regularly, but was
hoping to avoid having to type the names for each worksheet inorder to save
them?

Thanks for your help!

Tanya

Hi Tanya

Try this code example
http://www.rondebruin.nl/copy6.htm

--

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




"Tanya" wrote in message ...
I have a workbook with several worksheets (30) and am trying to find a way to
save each worksheet in the book as a new excel file using the name of the
worksheet as the new file name...I use the move or copy regularly, but was
hoping to avoid having to type the names for each worksheet inorder to save
them?

Thanks for your help!

Tanya

Worked exactly as I hoped. Thanks for the hours you've saved me! You make
this look so easy! --Tanya

"Ron de Bruin" wrote:

Hi Tanya

Try this code example
http://www.rondebruin.nl/copy6.htm

--

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




"Tanya" wrote in message ...
I have a workbook with several worksheets (30) and am trying to find a way to
save each worksheet in the book as a new excel file using the name of the
worksheet as the new file name...I use the move or copy regularly, but was
hoping to avoid having to type the names for each worksheet inorder to save
them?

Thanks for your help!

Tanya

An exact answer to your question! See Ron's site:

http://www.rondebruin.nl/copy6.htm
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tanya" wrote:

I have a workbook with several worksheets (30) and am trying to find a way to
save each worksheet in the book as a new excel file using the name of the
worksheet as the new file name...I use the move or copy regularly, but was
hoping to avoid having to type the names for each worksheet inorder to save
them?

Thanks for your help!

Tanya

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveWorkbook
.SaveAs FileName:=ActiveWorkbook.Path _
& "\" & w.Name & ".xlsx" 'change to .xls if not 2007 version
.Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Mon, 20 Apr 2009 12:37:03 -0700, Tanya
wrote:

I have a workbook with several worksheets (30) and am trying to find a way to
save each worksheet in the book as a new excel file using the name of the
worksheet as the new file name...I use the move or copy regularly, but was
hoping to avoid having to type the names for each worksheet inorder to save
them?

Thanks for your help!

Tanya

Look out Gord

If your default Save format in 2007 is not xlsx this will not work

--

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




"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveWorkbook
.SaveAs FileName:=ActiveWorkbook.Path _
& "\" & w.Name & ".xlsx" 'change to .xls if not 2007 version
.Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Mon, 20 Apr 2009 12:37:03 -0700, Tanya
wrote:

I have a workbook with several worksheets (30) and am trying to find a way to
save each worksheet in the book as a new excel file using the name of the
worksheet as the new file name...I use the move or copy regularly, but was
hoping to avoid having to type the names for each worksheet inorder to save
them?

Thanks for your help!

Tanya

Thanks for the tip Ron

Still trying to get used to 2007.


Gord

On Mon, 20 Apr 2009 22:13:48 +0200, "Ron de Bruin"
wrote:

Look out Gord

If your default Save format in 2007 is not xlsx this will not work

Hi Gord

I have it set a xlsm because most of my files have code
And a lot of people have it set to 97-2003

For others here you can change it in 2007 if you want
Office ButtonExcel Options...Save
Change "Save files in this format .............."


--

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




"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
Thanks for the tip Ron

Still trying to get used to 2007.


Gord

On Mon, 20 Apr 2009 22:13:48 +0200, "Ron de Bruin"
wrote:

Look out Gord

If your default Save format in 2007 is not xlsx this will not work

BTW

I checked your site to see what changes would have to be made.

http://www.rondebruin.nl/copy6.htm

Good work....................thanks.


Gord

On Mon, 20 Apr 2009 13:25:00 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Thanks for the tip Ron

Still trying to get used to 2007.


Gord

On Mon, 20 Apr 2009 22:13:48 +0200, "Ron de Bruin"
wrote:

Look out Gord

If your default Save format in 2007 is not xlsx this will not work