Break Sheets in a WB to individual WBs

[sjha]

Hello -

I export a excel file (master) from a website and that excel file has at least 25 sheets. My client has asked me to provide 25 separate workbooks instead of one WB with 25 sheets in it. I can do manually but to do this in a daily basis can be time consuming for me and therefore I am requesting your help. Is there a solution so that when I run it (template) it will break the master file into separate files? Also, If I can save those individual files in a specific folder that will be great as well. Please suggest as what is possible. Thank you for your help!!

 

[dave3009]

Try

Sub bustup()
Dim ws As Worksheet
Dim fPath As String
fPath = ThisWorkbook.Path
For Each ws In ThisWorkbook.Sheets
    ws.Copy
    ActiveWorkbook.SaveAs fPath & "\" & ws.Name
Next ws
End Sub

 

[jbeaucaire]

Try this, just edit the fPath to the directory you want the files saved into. The macro will save each sheet to a workbook of its own with the current date appended. (Sheet1 12-4-2009.xls)

Sub SaveIndividualSheets()
'JBeaucaire  (12/4/2009)
Dim ws As Worksheet, fPath As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False

fPath = "C:\Documents and Settings\Jerry\My Documents\Excel Files\"

For Each ws In ActiveWorkbook.Worksheets
    ws.Copy
    ActiveWorkbook.SaveAs fPath & ws.Name & " " & Format(Date, "MM-DD-YYYY") & ".xls", xlNormal
    ActiveWorkbook.Close
Next ws

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[sjha]

THANK YOU Both!!

jbeaucaire, OK..so far I am very happy...but getting a bit greedy here. OK, in the master file, each sheets always have department code in cell 'A5' and I'd like that to be part of the file name follwed by the date that you have implemented. Please suggest if this is possible? otherwise, I am very happy with the outcome.

Thanks again!

 

[jbeaucaire]

Sub SaveIndividualSheets()
'JBeaucaire  (12/4/2009)
Dim ws As Worksheet, fPath As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False

fPath = "C:\Documents and Settings\Jerry\My Documents\Excel Files\"

For Each ws In ActiveWorkbook.Worksheets
    ws.Copy
    ActiveWorkbook.SaveAs fPath & Range("A5").Text & " " & Format(Date, "MM-DD-YYYY") & ".xls", xlNormal
    ActiveWorkbook.Close
Next ws

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[sjha]

Hello jbeaucaire - I have some new *wishful* requirement. How is it possible that I create a template with your latest code but the code executes another file that has data? For example, name of the master file is always 'Master Departments November 2009.xls' and every month just the month and year will change. So, I'd like to have a solution so that I will have a template open and the 'Master Departments November 2009.xls' file open. And, when I run the macro in the template it executes what the code is about. Does it make sense? Please suggest...thank you again!!