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 Emersondi,

Am I correct in understanding that you want the macro to delete each source worksheet after the corresponding destination workbook is created?

If so, this would involve deletion of all sheets in the source workbook. To do this the code would have to delete the entire workbook, including the running macro. This is because a workbook must contain at least one worksheet.

Of course, the code could create a new blank worksheet, then delete what was previously the last worksheet. Is this what you want to do?

Damon

 

[emersondi]

Hi Emersondi,

Am I correct in understanding that you want the macro to delete each source worksheet after the corresponding destination workbook is created?

If so, this would involve deletion of all sheets in the source workbook. To do this the code would have to delete the entire workbook, including the running macro. This is because a workbook must contain at least one worksheet.

Of course, the code could create a new blank worksheet, then delete what was previously the last worksheet. Is this what you want to do?

Damon

Hi damon... not really... i don´t need to delete the original file... I just needed to convert 1 workbook with 10 worsheet in 10 workbook with one worksheet each... the problem i had was generated because the original file incluided inside a hiden sheet.. just unhided and the problem was solved... hope you can understand my english ... THanks for your help

 

[fayroberts]

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

How awesome is this?! Thank you so much - what a lifesaver.

 

[jamilm]

Dear All,

i see that there are many posts for this in Mrexcel forum, but seems like to proper answer. i have the code below, but it deletes by leaving one. so i assume somthing like "step" should be used but dont know how. any one could help me?

thanks.



Sub SplitSheets()
Dim W As Worksheet

On Error GoTo Err_SplitSheets

Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each W In Worksheets
W.SaveAs ActiveWorkbook.Path & "/" & W.Name
W.Delete
Next W

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

 

[moneytree1987]

the above is a step forward, but i am still looking for a macro that takes a single workbook and splits it into individual files for each sheet. I am very new to VB, is there a away to incorperate a move/copy to new workbook code in here some how that would illiminate the files with multiple tabs?

 

[Dentette]

Hello,

This formula is great. But I seem to have the same issue. I 'd like to see 1 worksheet in 1 excellfile. Could you please adapt the formula in order to have 1 worksheet per file?

I used this formula

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

Thanks in advance!!

 

[jevi]

Sub Copy_Sheets_As_New_Workbook()
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
ws.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Name
ActiveWorkbook.Close
Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[Dentette]

Great! Thanks

 

[VBAlearner2012]

I used the code provided in the post#17 but ran into Run-time error'1004': Method 'Copy' of object '_Worksheet' failed.

I am on MS 2010.

Can anyone point me to the right direction?

Greatly appreciated.

 

[cajomano]

I wrote a script that does just that (split excel worksheets into separate files) for my client, it's available for download here:

http://www.jeeshenlee.com/2012/05/jmc-excel-split-split-excel-worksheets.html

It seems this only works when the workbook has no more than 5 worksheets. Is that correct?