Split workbook into new file for each worksheet

[analyzethis]

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.

 

[Damon Ostrander]

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]

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!

 

[handk]

Hi Analyzethis

I wanted to split multi-sheet excel into single sheet excel,I found a

software called Excel Splitter from http://www.excelsplitter.com .Maybe

it can also help u
http://www.mrexcel.com/forum/member.php?u=23886

 

[telematiks]

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]

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]

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]

Can this code be modified to only apply to worksheet tabs that have a description beginning with "G"?
Thanks
Sabine

 

[byerssha]

I would think an if..then statement inside the for..next loop would do it.

Can this code be modified to only apply to worksheet tabs that have a description beginning with "G"?
Thanks
Sabine

 

[emersondi]

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!!!