Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print worksheets to different files (2003)

    I have multiple tabs in a workbook. I want to save each worksheet to a separate file. Is there an easy way to do this. Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print worksheets to different files (2003)

    Print? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    Here is a macro that will split a workbook into separate files.
    If your worksheets contain formulas that refer to other sheets, this may not work well.
    <code>
    Sub SplitWorkbook()
    Dim wbkCur As Workbook
    Dim wbkNew As Workbook
    Dim wsh As Worksheet
    Application.ScreenUpdating = False
    Set wbkCur = ActiveWorkbook
    For Each wsh In wbkCur.Worksheets
    wsh.Copy
    Set wbkNew = ActiveWorkbook
    wbkNew.SaveAs wsh.Name
    wbkNew.Close
    Next wsh
    Application.ScreenUpdating = True
    End Sub</code>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print worksheets to different files (2003)

    I get an error at wbknew.close

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print worksheets to different files (2003)

    What does the error message say? I tested the code before I posted it, so it does work.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print worksheets to different files (2003)

    It says Code execution has been interrupted and then it highlights wbknew.close. Then it saves only the first worksheet.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print worksheets to different files (2003)

    Do you have worksheets with unusual names?

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print worksheets to different files (2003)

    Sheet1, Sheet2, Sheet3 - I didn't name them.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print worksheets to different files (2003)

    I'm afraid I don't understand what causes the error. Could you post a copy of the workbook? Remove or alter sensitive content from the copy.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print worksheets to different files (2003)

    I rebooted and I am not getting the error but I am not sure what is happening. I named the 3 tabs Mary, Linda, Walt. When I ran the macro it created one separate worksheet called Book1 but the other 2 worksheets didn't create. Is the macro supposed to separate the sheets into different files and then you save each one where you want? It only creates on worksheet of the first sheet.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print worksheets to different files (2003)

    When I run the macro in a workbook with worksheets Mary, Linda and Walt, it creates three new workbooks Mary.xls, Linda.xls and Walt.xls.
    Are you sure that the correct workbook was active when you ran the macro?

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print worksheets to different files (2003)

    Sorry to get back to this. The macro is saving the worksheets as separate files but I am not sure where the files are being saved. I have to do a search to find them. I thought they would be in the same folder where the original spreadsheet is but that is not the case.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print worksheets to different files (2003)

    Thank you

  13. #13
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print worksheets to different files (2003)

    In my running of the macro, the files were saved to the default file location.

    To view the default file location (in Excel 2000 at least) go to:
    Tool/ Options... / General tab

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print worksheets to different files (2003)

    If you want them to be in the same folder as the original workbook, you can change the line
    <code>
    wbkNew.SaveAs wsh.Name
    </code>
    to
    <code>
    wbkNew.SaveAs wbkCur.Path & "" & wsh.Name
    </code>
    If you want to save them to a specific path, you can include that:
    <code>
    wbkNew.SaveAs "C:MyFolderMySubFolder" & wsh.Name</code>

  15. #15
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print worksheets to different files (2003)

    Well this is certainly useful.

    I am using it with version 2007 and it works fine. I was wondering though. How could i change it to save the split files to version 2003???
    I can always save the workbook to 2003 prior to running the macro though, I guess, if that would be easier.

    Great code.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •