VBA - Saving individual sheets from master workbook as separate files

[eurobonds]

Hi, VBA noob here...

I have a master workbook with quite a few number of sheets. I want a quick way to save each sheet to a separate file with a custom name (ie, to cycle through all visible sheets, deleting out all other sheets and then saving a single sheet workbook). I do have several hidden data sheets that need to be in each individual file.

I could probably do this with brute force, ugly code, but I'm sure one of the experts here knows a fast way!

Thanks

 

[Norie]

It's quite straightforward to save a single sheet to a new workbook, gets a bit more complicated if other sheets need to go along.

What single sheets would you want to save in their own workbooks and what 'data' sheets would be required to go with them?

 

[eurobonds]

the master file has 30 visible sheets and 4 hidden sheets. I want to end up with 30 separate files, each with one of the visible sheets and the 4 hidden sheets.

 

[Norie]

Do you know the names of the hidden sheets?

That's not essential but it would make things a lot easier.

For example, this code will save all the visible sheets and 4 other, hidden, named sheets to new individual workbooks.

Dim ws As Worksheet
Dim arrShts

    arrShts = Array("Hidden1", "Hidden2", "Hidden3", "Hidden4", "")

    For Each ws In ThisWorkbook.Sheets
        If ws.Visible Then
            arrShts(4) = ws.Name
            ThisWorkbook.Sheets(arrShts).Copy

        End If
    Next ws

Obviously you would replace Hidden1, Hidden2 etc with the actuall names of the hidden sheets to be copied

PS I've not included any code to save the new workbooks, not sure what filename/type you would want to use.

 

[eurobonds]

Thanks, Norie. Yes, I know the name of the hidden sheets. This is a great start. If you want, could you provide the code to save as well? The file type would be xlsx. The file name structure for each of the 30 files would be:

"master workbook name" + "sheet name".xlsx

 

[Norie]

Try this, change the path/folder.

Dim wbNew As Workbook
Dim ws As Worksheet
Dim arrShts
Dim strPath As String

    arrShts = Array("Hidden1", "Hidden2", "Hidden3", "Hidden4", "")

    strPath = "C:\Test\"

    For Each ws In ThisWorkbook.Sheets
        If ws.Visible Then
            arrShts(4) = ws.Name
            ThisWorkbook.Sheets(arrShts).Copy
            Set wbNew = ActiveWorkbook
            With wbNew
                .SaveAs FileName:=strPath & ThisWorkbook.Name & "-" & ws.Name & ".xlsx", FileForma:=xlOpenXMLWorkbook
                .Close
            End With
        End If
    Next ws

 

[eurobonds]

Works perfectly. You just saved me a lot of time!
Thanks

 

[sd266]

What if I have a workbook with 10 Sheets and I want to export sheet1,3,4 into 3 different excel Files?

Try this, change the path/folder.

Dim wbNew As Workbook
Dim ws As Worksheet
Dim arrShts
Dim strPath As String

    arrShts = Array("Hidden1", "Hidden2", "Hidden3", "Hidden4", "")

    strPath = "C:\Test\"

    For Each ws In ThisWorkbook.Sheets
        If ws.Visible Then
            arrShts(4) = ws.Name
            ThisWorkbook.Sheets(arrShts).Copy
            Set wbNew = ActiveWorkbook
            With wbNew
                .SaveAs FileName:=strPath & ThisWorkbook.Name & "-" & ws.Name & ".xlsx", FileForma:=xlOpenXMLWorkbook
                .Close
            End With
        End If
    Next ws