• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Splitting Worksheets into individual workbooks

accountingblm

New Member
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

Excel Ninja
Staff member
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

New Member
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

Excel Ninja
Staff member
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
 
Top