Split workbook into new file for each worksheet

analyzethis

New Member
Joined
Jan 28, 2004
Messages
8
new to board and VB, but learning...
2-part question:
1. I have a large workbook that I need to split into individual files- one for each worksheet. Each filename needs to be the worksheet name and should be saved in the same path as the original workbook (it may not always be the same path...)

2. Each of these files will have 2 worksheets to begin with. Sheet1 will have tracking numbers added down column A. Sheet2 will be a template worksheet. I need to add a copy of the template before the last worksheet that has the worksheet name form the last entry from sheet1, column A. My intent is to have the user run the macro after each new entry on sheet1 to provide a new worksheet, and keep all the additional worksheets in the same workbook.

I've tried to piece together bits from different entries on this board, but not having any luck.
Thanks in advance to any help anyone can provide.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi analyzethis,

I believe the code below should address your first question. I don't quite understand your second question, so perhaps a bit of clarification or an example would be helpful.

Sub SplitSheets()
Dim W As Worksheet
For Each W In Worksheets
W.SaveAs ActiveWorkbook.Path & "/" & W.Name
Next W
End Sub
 

analyzethis

New Member
Joined
Jan 28, 2004
Messages
8
Thanks, Damon- that did it!
I know - clear as mud...I'l have a workbook with 2 worksheets. On Sheet1 the user will enter a tracking number in column A, continuing on each row for each new number. Sheet2 will be a template for data that must be filled in for each tracking number. I'd like to copy the template (sheet2) as an additional worksheet before the sheet2 tab, using the last cell entered on sheet1 as the sheet name. This user would activate this macro as each new tracking number is entered. I hope I did better that time!
 

telematiks

New Member
Joined
Sep 20, 2010
Messages
1

ADVERTISEMENT

This was extremely helpful! I modified it a bit (see below), but the problem I get when I have it append to the ActiveWorkbook.Name is that successive worksheets accumulate the full filename of the prior CSV. For example: If my first tab becomes "Report_1.csv", the second tab becomes "Report_1.csv_2.csv".

Sub SplitSheets()
Dim W As Worksheet
For Each W In Worksheets
W.SaveAs ActiveWorkbook.Path & "/" & Trim(ActiveWorkbook.Name) & "_" & W.Name & ".csv", FileFormat:=xlCSV
Next W
End Sub


Thanks!
 

byerssha

New Member
Joined
Jul 6, 2008
Messages
7
This is exactly what I've been looking for. Thanks for posting!

Hi analyzethis,

I believe the code below should address your first question. I don't quite understand your second question, so perhaps a bit of clarification or an example would be helpful.

Sub SplitSheets()
Dim W As Worksheet
For Each W In Worksheets
W.SaveAs ActiveWorkbook.Path & "/" & W.Name
Next W
End Sub
 

byerssha

New Member
Joined
Jul 6, 2008
Messages
7

ADVERTISEMENT

Hi, This works great for me except for one thing.. I'd like to drop the macros from the new files, so they won't confuse or upset the recipients. How would I do that? i.e. the new files are all named *.xlsm and have my macros in them. I can do a batch ren in dos to change the file extension, but that doesn't get rid of the macros. Thanks.

Hi analyzethis,

I believe the code below should address your first question. I don't quite understand your second question, so perhaps a bit of clarification or an example would be helpful.

Sub SplitSheets()
Dim W As Worksheet
For Each W In Worksheets
W.SaveAs ActiveWorkbook.Path & "/" & W.Name
Next W
End Sub
 

sdamico

New Member
Joined
Oct 25, 2010
Messages
1
Can this code be modified to only apply to worksheet tabs that have a description beginning with "G"?
Thanks
Sabine
 

emersondi

New Member
Joined
May 5, 2011
Messages
2
Hi!!!. thanks for your marco your macro is short and I like it!

but i have a problem ... i had a file with 10 sheets.. used the macro and create the 10 files but in the 10 new workbooks shows as active the worksheet of the file name.. but didn't delete the others worksheet... can you help me??

i this was the macro i used..
Sub SplitSheets()
Dim W As Worksheet
For Each W In Worksheets
W.SaveAs ActiveWorkbook.Path & "/" & W.Name
Next W
End Sub

Thanks for help.. and sorry for my english!!!
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,539
Members
425,480
Latest member
br400821

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