Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    343
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Saving worksheets to separate files (Excel 97)

    I have multiple worksheets located in one file and I'd like to remove or copy each worksheet from the one file into their own separate files. For example file.xls has 5 worksheets named sheet1, sheet2, sheet3, sheet4 and sheet5. I'd like sheet1 to be saved to a new file called file1.xls, and sheet2 to a new file called file2.xls... etc.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving worksheets to separate files (Excel 97)

    Well, my solution isn't exactly elegant, but what I usually do is simply highlight the entire worksheet by clicking in the upper left corner, right click -select copy. Then create a new workbook and paste the copied sheet in. Then save it as whatever name I like.
    I'm fascinated to see how this can be done more quickly and "professionally".

  4. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    343
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Saving worksheets to separate files (Excel 97)

    Thank you for your reply, however I should have worded my question a bit differently, as I have about 30 worksheets that I want to save to 30 different files, and I was wondering if there was a fast and quick way to do so without having to do the copy and paste and save as... thanks anyway... I was thinking maybe there was a macro out there or something.

  5. #4
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving worksheets to separate files (Excel 97)

    LJM,
    I put this little ditty together:

    <font face="Georgia">Sub Breakout()
    '
    ' BreakOut Macro
    ' removes all worksheets, leaving worksheet #1
    ' All worksheets saved to active directory in their own name.
    '
    Dim ShHome As String
    ShHome = ActiveSheet.Name

    For Each Worksheet In Worksheets
    Worksheets(2).Move
    ActiveWorkbook.SaveAs (ActiveSheet.Name & ".xls")
    ActiveWorkbook.Close
    Next Worksheet
    End Sub</font face=georgia>

    It does nothing with the first worksheet in the bunch, but you can save that outside the loop if you want.

    [Added after first posting:}
    The ShHome variable can help you recognize the "main" worksheet, and prevent it form being moved out, as
    <font face="Georgia">If activeworksheet.name=ShHome then exit sub</font face=georgia>

    Errol

  6. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Saving worksheets to separate files (Excel 97)

    This should copy each sheet to a new file and name them sequentially :<pre> Sub SheetsToFiles()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, intIndex As Integer
    For Each ws In ThisWorkbook.Worksheets
    ws.Copy
    intIndex = intIndex + 1
    With ActiveWorkbook
    .SaveAs "File" & intIndex & ".xls"
    .Close
    End With
    Next
    Application.ScreenUpdating = True
    End Sub</pre>

    The original workbook is left intact.

    Andrew C

  7. #6
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving worksheets to separate files (Excel 97)

    The macros already given in this thread should do the job for a large number of sheets but to make a one off/ ad hoc copy of a sheet then:
    click Edit
    then click Move or Copy Sheet
    and explore the options in the dialog box that appears

    You can also get at this feature by right clicking on the sheet name tab.

    (not) stuck

  8. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    343
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Saving worksheets to separate files (Excel 97)

    Thank you, Thank you! That macro worked perfectly!

    Now to complicate things a bit more... before saving all the worksheets to their own files, can I have the macro look at each worksheet and adjust the column to width automatically, and do a copy paste special values for each worksheet?

    What we have is an ADP download of peoples vacation and flex balances, and we want to email each supervisor their employees vac & flex balances. I've used the download from ADP to make a pivot table, I then did a "show pages" function based on the supervisor's name... this creates the 30 or so worksheets I mentioned in the first request. Since I don't want everyone to see everyone elses balances I want to copy paste values so the pivot table is no longer a pivot table, plus I want the column widths to be adjusted as well, as some peoples names are longer than others.

    Last but not least (I'm having doubt about this one) is there a way to have these newly created files attached to separate emails, going to the correct person based on the file name (the worksheets, and then the new file names based on the worksheet name are the supervisor names). We use lotus notes release 6.

    Anyone got any ideas?!?!?!

    Thanks so much!!!!!

  9. #8
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving worksheets to separate files (Excel 97)

    LJM,
    For your next two wishes.... <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    I learned lots of stuff by this method:
    1. Click on Tools|Macros|Record New Macro
    2. Choose to store the new macro in a new workbook (to protect the integrity of your original files)
    3. Then do your thing. In this case, select/copy/paste special(values). Select the columns you want to resize, double-click on the column dividers (up near the ABC's). Then stop the maco.
    4. Open the Macro editor to see your new program. There's the commands you'll need.

    The automatic macro often adds extra commands, but you should be able to figure out what you need.

    Seriously, if this just doesn't stick in your head, or you'd rather learn Senegalese than VBA, let me know. These two things are simple enough to do.

    As for the email thing, I bet it can happen, but I've never worked on that type of program.

    Errol

  10. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,406
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Saving worksheets to separate files (Excel 97)

    Andrew,
    Would this work if I wanted to, say, save three (3) of five (5) worksheets from one workbook/file to one (1) nes workbook/file?
    Thanks.

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

    Re: Saving worksheets to separate files (Excel 97)

    Andrew's code will copy each sheet to a different file, not some of the sheets to one file.

  12. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,406
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Saving worksheets to separate files (Excel 97)

    Hans,
    Do you (or anyone else) know of a code that will copy only selected worksheets from a workbook into ONE new workbook (without having to copy each of the selected worksheets individually)?
    Thanks.

  13. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Saving worksheets to separate files (Excel 97)

    Yes. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    ActiveWindow.SelectedSheets.Copy

  14. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving worksheets to separate files (Excel 97)

    Hi

    Hans solved this problem for me with the the attached code, Create a simple User Form and use this code,


    It will allow you to copy 1 or any number of visible worksheets to a new workbook. (Please note the password part of the code you will need to set this to your own password if any!)

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Saving worksheets to separate files (Excel 97)

    Braddy,

    The code you attached is incomplete, moreover, the complete version (from the thread starting at <post:=558,147>post 558,147</post:>) acts differently from what has been discussed here: it copies values only, not the entire worksheet including formulas. It's up to Jeff Kirk to decide if he can use it.

  16. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving worksheets to separate files (Excel 97)

    Hi Hans

    I apologise If I have made an error here, because I get so much from this forum I was trying to give a little back, I will be more careful in future what I post.

    Once again apologies for any misunderstanding.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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