Break Sheets in a WB to individual WBs

sjha

Active Member
Joined
Feb 15, 2007
Messages
355
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!!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Try

Code:
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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)
Code:
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

Active Member
Joined
Feb 15, 2007
Messages
355
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
Rich (BB code):
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

Active Member
Joined
Feb 15, 2007
Messages
355
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,396
Messages
5,601,420
Members
414,449
Latest member
Pashtun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top