Splitting Worksheets into individual workbooks

[accountingblm]

I'm having some issues with a code I've been using to seperate a workbook with multiple worksheets into individual workbooks. The code works great, but i would like it to dump off all the other background tabs. So when it save the tab to it's own workbook it doesn't bring all the other tabs with it. Here is the code i have been using.


Sub SplitSheets()

Dim W As Worksheet

For Each W In Worksheets

W.SaveAs ActiveWorkbook.Path & "/" & W.Name

Next W

End Sub

 

[Hui]

Accountingblm,


try the following

======================


Sub SplitSheets()

Dim W As Worksheet


WPath = ActiveWorkbook.Path

Startsheet = ActiveSheet.Name


For Each W In Worksheets

Sheets(W.Name).Select

Sheets(W.Name).Copy


ActiveWorkbook.SaveAs Filename:=WPath & "/" & W.Name

ActiveWindow.Close

Next W

Sheets(Startsheet).Select

End Sub

 

[accountingblm]

Hui- Thanks for your help that works perfectly. I was wondering if there was a way to have the macro save the files as excel 97-2003 format?

 

[Hui]

Try the following

I don't use 2007 at home and so can't test it

But it works in 2010


Sub SplitSheets()

Dim W As Worksheet


WPath = ActiveWorkbook.Path

Startsheet = ActiveSheet.Name


For Each W In Worksheets

Sheets(W.Name).Select

Sheets(W.Name).Copy


ActiveWorkbook.SaveAs Filename:=WPath & "/" & W.Name, FileFormat:=xlExcel8

ActiveWindow.Close

Next W

Sheets(Startsheet).Select

End Sub

 

[accountingblm]

Worked perfectly thanks again.