Thread: Split worksheets to separate files

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?