Split worksheets to separate files

[ksteffin]

Hello all. I am working on a project that is two part. I am working with Excel 2007. I have a workbook that has 50 or so worksheets that I need to split and save as individual files. I found the following code online. It works fine, however, I need the "new" workbooks to be saved as Excel 97-2003 files. What do I need to add to this to make that happen?

Sub splitsheettoworkbook()

Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Worksheet 'Could be chart, worksheet, Excel 4.0 macro,etc.
Dim strSavePath As String

On Error GoTo ErrorHandler

Application.ScreenUpdating = False 'Don't show any screen movement

strSavePath = "C:\Excel Worksheets\" 'Change this to suit your needs

Set wbSource = ActiveWorkbook

For Each sht In wbSource.Sheets
sht.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close 'Remove this if you don't want each book closed after saving.
Next

Application.ScreenUpdating = True

Exit Sub

ErrorHandler: 'Just in case something hideous happens
MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "."

End Sub

Part Two is this: I have another file that I'm needing to split as well. However, the information in this workbook is organized in a Pivot Table. I have utilized the 'Show Report Filter Pages' to have the information on separate worksheets so that I can split the file. Will the above code (with the modification to save in 97-2003 format) work with the Pivot Table?

[aravindhan_31]

Try

wbDest.SaveAs strSavePath & sht.Name

change this to

wbDest.SaveAs strSavePath & sht.Name & ".xls"

[ksteffin]

That works great! One more question. The original workbook contains reports that I need to split and send out each month. If i wanted to add "March Reports" to the sheet name only when saving it, how would I do that? Could I add "March Reports" like this?

wbDest.SaveAs strSavePath & sht.Name "March Reports" & ".xls"

Thanks for your help, aravindhan_31!

[Sonam Sahani]

I tried using this macro, however while opening the splitted worksheet I am getting the error"The file you are trying to open, " ", is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file" , whereas the original file which I am trying to split is also in .xls format. I do not want this error. Please help

[Fotis1991]

Sonam Sahani

Unfortunately you have to start your own thread for this.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.