Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Copying/Saving Worksheets from a large workbook into separate files

This is a discussion on Copying/Saving Worksheets from a large workbook into separate files within the Excel Questions forums, part of the Question Forums category; I'm trying to copy and save worksheets from a large workbook into separate files. I also need to have the ...

  1. #1
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Unhappy Copying/Saving Worksheets from a large workbook into separate files

    I'm trying to copy and save worksheets from a large workbook into separate files. I also need to have the worksheets saved with the name of the tab as their file names and in the same path as the original workbook (which changes each month). The original workbook must remain intact. I've read several suggestions on other sites but most delete the original when saving the worksheets separately. Any direction that anyone can give would be greatly appreciated!!! I'm so lost on this one....

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,967

    Default Re: Copying/Saving Worksheets from a large workbook into separate files

    Don't know what you've tried but perhaps something like this.
    Code:
    Sub CreateNewWBS()
    Dim wbThis As Workbook
    Dim wbNew As Workbook
    Dim ws As Worksheet
    Dim strFilename As String
    
        Set wbThis = ThisWorkbook
        For Each ws In wbThis.Worksheets
            strFilename = wbThis.Path & "/" & ws.Name
            ws.Copy
            Set wbNew = ActiveWorkbook
            wbNew.SaveAs strFilename
            wbNew.Close
        Next ws
    End Sub
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Default Re: Copying/Saving Worksheets from a large workbook into separate files

    This is awesome...I've just tested it and it was totally successful. AWESOME!!!!!! I got so hung up on the path...this is perfect...A MILLION TIMES - Thank you!!!

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,967

    Default Re: Copying/Saving Worksheets from a large workbook into separate files

    Be careful with the path and the filename.

    You might have some sheet names that won't be valid filenames, and the code I posted doesn't check for that.
    If posting code please use code tags.

  5. #5
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Default Re: Copying/Saving Worksheets from a large workbook into separate files

    okay...I've corrected how my tabs are named to avoid issues when the spreadsheets are copied out into their own filenames. Now, I need to figure out how to make it copy beginning with a certain sheet (a different sheet in each workbook). My thought was to add in the following - but it doesn't work - it stops on the copy line....is it my placement of where I'm defining i?

    Sub CreateNewWBS()
    Dim wbThis As Workbook
    Dim wbNew As Workbook
    Dim ws As Worksheet
    Dim strFilename As String

    i=4

    Set wbThis = ThisWorkbook
    For Each ws In wbThis.Worksheets
    strFilename = wbThis.Path & "/" & ws.Name
    ws.Copy After:=ws(i)
    Set wbNew = ActiveWorkbook
    wbNew.SaveAs strFilename
    wbNew.Close
    Next ws
    End Sub

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,967

    Default Re: Copying/Saving Worksheets from a large workbook into separate files

    I'm not sure what you mean.

    I thought you wanted to create a new workbook for each worksheet containing only that worksheet and with a filename based on the worksheet name.

    Now you've mentioned different workbooks? What other workbooks are you referring to?

    The only ones there should be are the original and a workbook for each worksheet in the original.
    If posting code please use code tags.

  7. #7
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Default Re: Copying/Saving Worksheets from a large workbook into separate files

    Sorry....I've got 4 different workbooks and the answer you gave works perfectly...I was just trying to figure out how best to make it begin with a certain worksheet within a workbook (which may be a different worksheet depending on the workbook that I'm using this macro in)...does that makes sense? One workbook has 40 worksheets and I only need to separate out 34 into their own files. Another workbook has 32 worksheets and I only need to separate out 28 into their own files. I use the i = 4 or i = whatever in another macro and it works fine.....I just wanted to try and incorporate it into what you advised as well. Any thoughts?

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,967

    Default Re: Copying/Saving Worksheets from a large workbook into separate files

    Is there any way to determine which worksheets to include/exclude?

    Perhaps you only want to copy worksheets with a particular name?

    Or do you have a list of the worksheets you want to copy?

    In theory you could use the index but it's probably not a good idea because it's Excel that sets that in the first place.
    If posting code please use code tags.

  9. #9
    New Member
    Join Date
    Aug 2010
    Posts
    11

    Default Re: Copying/Saving Worksheets from a large workbook into separate files

    hmm....the number of sheets that need to be copied out into separate files could change. That's why I was trying to have it start after a particular sheet that I know will not need to be copied out. One workbook may need all but the first 4 sheets copied out. Another workbook may need all but the first 8 sheets to be copied out. I think that's why I was trying to use the index to notate which sheet to start copying.

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,967

    Default Re: Copying/Saving Worksheets from a large workbook into separate files

    How would you determine yourself what worksheets to copy?

    Like I said you could possibly use the Index but I wouldn't rely on it.

    What appears to you to be the 4th worksheet might not be what Excel considers the 4 worksheet.
    If posting code please use code tags.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com