Split workbook into new file for each worksheet

A

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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
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
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
This is exactly what I've been looking for. Thanks for posting!

Damon Ostrander said:
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
Click to expand...
 
Upvote 0
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.

Damon Ostrander said:
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
Click to expand...
 
Upvote 0
Can this code be modified to only apply to worksheet tabs that have a description beginning with "G"?
Thanks
Sabine
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Checking for Worksheets that already exist
2
Replies
11
Views
382
Logit
Logit
C
  • Solved
Split into worksheets based on multiple criteria
Replies
7
Views
194
Fuji
F
P
  • Solved
New Worksheet Hyperlinks
2
Replies
12
Views
269
Peter_SSs
Peter_SSs
excelbytes
  • Solved
Need VBA Code To Save File As To New Location With New Name
Replies
3
Views
331
6StringJazzer
6StringJazzer
S
  • Question Question
2 questions related to VBA
Replies
4
Views
82
Skyybot
Skyybot

Forum statistics

Threads
1,224,813
Messages
6,181,112
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top