Extract and Save multiple worksheets to own workbook

[Samson92]

Hi, I have a workbook that contains 32 separate sheets inside it. I would like to copy 6 of the sheet from it, but copy them to their own individual workbook and save each one as the tab name, and have them save to my desktop. Example of what I mean, some of the tabs I want are named "Accounts Open", "Accounts Close", "Accounts Withdraw" and I would like to have the workbooks named as these.

Any advice on the best way to do this?

Thanks.

 

[Norie]

This code will copy the 'Accounts Open', 'Accounts Close' and 'Accounts Withdrawn' sheets to new, individual workbooks and save them to the desktop.

Sub SaveStuff()
Dim wbNew As Workbook
Dim strFileName As String
Dim strPath As String
Dim arrSheets()
Dim I As Long

    arrSheets = Array("Accounts Open", "Accounts Close", "Accounts Withdrawn")

    strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    
    For I = LBound(arrSheets) To UBound(arrSheets)
    
        strFileName = arrSheets(I)
        
        ActiveWorkbook.Sheets(arrSheets(1)).Copy
        
        Set wbNew = ActiveWorkbook
        
        With wbNew
            .SaveAs Filename:=strPath & Application.PathSeparator & strFileName, FileFormat:=xlOpenXMLWorkbook
            .Close SaveChanges:=False
        End With
        
    Next I
    
End Sub

 

[gallen]

Hello,

This does as you ask, you need to change the location and the the names of the sheets

Sub SaveSheets()
    Dim sh As Worksheet
    Dim sLoc As String
    
    '***Change below as required
    sLoc = "C:\Test\" 'Ensure to put the backslash '\' at the end
    
    'loop thorugh all sheets
    For Each sh In ThisWorkbook.Worksheets
        
        Select Case sh.Name
            'Enter the names of all sheets separated by a comma as below
            Case "Accounts Open", "Accounts Close", "Accounts Withdraw"
                sh.Copy
                ActiveWorkbook.SaveAs Filename:=sLoc & sh.Name
                ActiveWorkbook.Close
        End Select
    Next sh
    
End Sub

 

[Samson92]

Thank you both for your help, exactly what I needed!