Create excel workbooks out of many sheets in one workbook

vartanu,

1) Can we assume that the original file has already been saved, and that you want the new workbooks written to the same directory as the original?

2) Which version of Excel are you using?  If Excel 2007 or later, which format do you want?  (xls, xlsx, xlsm, xlsb)

Patrick

1) Yes
2) excel 2003, xls
 
tnx

Excellent, that makes it much easier :)

Sub BreakItUp()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & ActiveSheet.Name & ".xls"
        ActiveWorkbook.Close False
    Next

    MsgBox "Done"

End Sub

Select allOpen in new window

I just added in the code as it is, and is doing the first sheet and then when it shold move to the next it still does the first and ask if i want to replace the sheet in the folder coz is allready there. And basicly does the same thing in a loop.
I tried also giving a path but didn't work

Try this.  I just tested it successfully.

Sub BreakItUp()

    Dim ws As Worksheet

    Application.DisplayAlerts = False
    
    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & ActiveSheet.Name & ".xls"
        ActiveWorkbook.Close False
    Next
    
    Application.DisplayAlerts = True
    
    MsgBox "Done"

End Sub

Select allOpen in new window

Stil not working, i tried also with a new workbook just with the 3 dafault sheets, and it always seems like is creating all 3 of them, but actualy creates only the active sheet.

It is working perfectly for me.  Please try this:

1) Create a new folder, perhaps c:\foo

2) Create a new Excel workbook, make sure it has 3 worksheets named Sheet1, Sheet2, and Sheet3

3) And add my updated code to that workbook, and then save that file in c:\foo

4) Run the macro

When it is done, you should have in that folder three new files: Sheet1.xls, Sheet2.xls, and Sheet3.xls

Tried exactly folowing your steps, still not working. Could be some limitations as i am on a corporate computer with some limited rights.
I always get just the active sheet made.
As long as it creates one sheet i think my steps are fine, could something be wrong with the code? like "Active Workbook" or something that will restrain the macro on creating just the active sheet

Are you putting the code into a module of the workbook you want to split, or another file (like personal.xls)? Patrick's code is designed to be run from within the to-be-split workbook

If not, could it be your workbook contains charts instead of standard worksheets?

Matt

Thanks for jumping in, Matt!

vartanu, I should have made it more explicit: my code is exporting the WORKSHEETS only, so if you have Chart sheets, those will not get exported.

Patrick

Is not about charts or anything, because i just create a blank workbook, and type something in every A1 cell in each sheet, but the script only creates the active sheets, even tough it blinks and looks like is doing the same thing twice.
 
When i go to the folder where my workbook is i find  only one sheet (the active sheet at the time i run the macro).
I think it has to do with my computer or access rights, but then it should not create one sheet and then stop.

vartanu,

I do not know what to say.  The code works exactly as designed when I run it, and presumably it works for mvidas as well.

Please upload a copy of your workbook, first obfuscating any sensitive data.

Patrick

The code might work for you guys and i am not blaming anybody here, just that for me at my corporate limited PC doesn't. Thanks all for your effort.