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.

 

[TexCell]

This is awesome thanks!

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

 

[crabby09]

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.

I believe this is because you have hidden tabs.

crabby