save worksheet to a seperate new file

[terdampar]

I have a workbook every month that contains of several worksheets. The
total of these worksheets are different from month to month depending
on which branch is active.

My problem is I need to save each worksheet to a new separate file
every month. I think I need to create Macro but just don't know how.

For example the workbook this month contains 8 tabs/sheets like:
branch 3; branch 8; branch 11; etc.
And last month it contained only 6 tabs/sheets.

The other problem is I need to name the new file just like the name of
respective sheet. So for instance the new file for sheet Branch 3
would be name as Branch 3.xls in the new folder.

Anybody please help. Thank you.

 

[Shane Devenshire]

Hi,

Without a macro:
1. Choose Window, Arrange, Tile, OK.
2. Resize the window so you can see the blank area behind it.
3. Hold down the Ctrl key and drag a sheet into the blank area and drop it,
then release the Ctrl key. - New file, one sheet. Do a file save as...
repeat as necessary.

Here is sample code you might modify to do your task automatically.

Sub CreateSheetFiles()
Dim sh As Worksheet
For Each sh In Worksheets
sh.Copy
ActiveWorkbook.SaveAs Filename:="C:\Users\Shane\Documents\" &
sh.Name & ".xls"
ActiveWindow.Close
Next sh
End Sub

 

[Ron de Bruin]

See also this example
http://www.rondebruin.nl/copy6.htm

 

[Gord Dibben]

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"
.Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

If not using Excel 2007 change the .xlsx to .xls

Saves to same folder as activeworkbook.

To change that......................

SaveAs FileName:="C:\Gordstuff\" & _
"\" & w.Name & ".xlsx"

Will overwrite the previous months files without warning.

If you don't want the overwrit, post back..............can correct it by
incrementing a number onto the name.



Gord Dibben MS Excel MVP

 

[terdampar]

Hi,

Without a macro:
1. Choose Window, Arrange, Tile, OK.
2. Resize the window so you can see the blank area behind it.
3. Hold down the Ctrl key and drag a sheet into the blank area and drop it,
then release the Ctrl key.  - New file, one sheet.  Do a file save as....
repeat as necessary.

Here is sample code you might modify to do your task automatically.

Sub CreateSheetFiles()
    Dim sh As Worksheet
    For Each sh In Worksheets
        sh.Copy
        ActiveWorkbook.SaveAs Filename:="C:\Users\Shane\Documents\" &
sh.Name & ".xls"
        ActiveWindow.Close
    Next sh
End Sub

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire









- Show quoted text -

That works well for copying. How about move it all except one
worksheet (HO (Home Office) is the name of the worksheet that is
supposed to be left in the original workbook)?

I am asking for automatication since I receive like 15 workbooks every
month and at the same day I have to spread every worksheet in those
workbooks into single separate file.
It's no-brainer to do that manually but if I could do it by Macro I'd
save like 2 hours every that day

Thank you so much!

 

[Ron de Bruin]

Hi Gord

Only changing the extension to xlsx is not OK Gord.
See my link for a solution for every Excel version

--

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

 

[Ron de Bruin]

Only changing the extension to xlsx is not OK Gord.

Change your default Save to xlsm or so then you see why.


--

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

 

[Gord Dibben]

Thanks Ron

Will get used to this 2007 stuff in the fullness of
time..............maybe<g>


Gord