Copying/Saving Worksheets from a large workbook into separate files

[FrustratedoverVB]

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....

 

[Norie]

Don't know what you've tried but perhaps something like this.

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

 

[FrustratedoverVB]

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!!!

 

[Norie]

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.

 

[FrustratedoverVB]

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

 

[Norie]

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.

 

[FrustratedoverVB]

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?

 

[Norie]

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.

 

[FrustratedoverVB]

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.

 

[Norie]

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.