Save multiple sheets that contain name “sheet…” as individual workbooks

Question

How do I save a specific sheet to a new workbook using Excel VBA?

I have multiple sheets with names "Sheet1", "Sheet2", "Sheet3" and so on.

I'd like to save all, in individual workbooks, with a single click.

This is returns an alert

Method Save as of object workbook failed

Sub SaveSplitSheet()
    Dim ws As Worksheet
    Dim wb As Workbook
    
    For Each ws In ThisWorkbook.Sheets
        If ws.Name Like "Sheet" & "*" Then
            Application.DisplayAlerts = False
            ws.Copy
            ActiveWorkbook.SaveAs "/Users/Tukiyem/Downloads", FileFormat:=56
            ActiveWorkbook.Close SaveChanges:=True
  
            Application.DisplayAlerts = True
        End If
    Next
End Sub

Found the answer-> the code below saves multiple sheets that contain name "sheet...." as individual workbooks.

Sub SaveAsInLoop()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        If ws.Name Like "Sheet" & "*" Then
            Application.DisplayAlerts = False
            ws.Copy
            ActiveWorkbook.SaveAs "/Users/Tukiyem/Downloads/" & ws.Name & ".xlsx", FileFormat:=51
            ActiveWorkbook.Close SaveChanges:=True
            Application.DisplayAlerts = True
        End If
    Next
End Sub

Also where do you want to put your copied worksheet? Currently, your code finds it, copies it and closes the workbook. — Samuel Everson
Hi Samuel, its excel. i want to put in this location /Users/Tukiyem/Downloads . the code above still return error. — user3357429
What my last comment means is, you are copying the Worksheet but then closing and saving the Workbook (which is what your worksheets are in). Generally if you are copying a Worksheet you would want to put it in a new Worksheet in the same Workbook OR even in a whole new Workbook. If you simply want to save the workbook in a new location, you can just use the line ActiveWorkbook.SaveAs "/Users/Tukiyem/Downloads", FileFormat:=56 Though you are always better to use a full file path and include a / at the end. — Samuel Everson
Does this answer your question? How to copy only a single worksheet to another workbook using vba — Samuel Everson

Samuel Everson Samuel Everson · Accepted Answer · 2020-05-02T09:57:25

I would slightly tweak your code to a For...Next loop rather For Each...Next which will allow the evaluation of which number sheet we are up to in the loop.

This code is an example of how to loop through the worksheets. It will print each sheet name to the Immediate window of the VBE.

Just adapt your SaveAs code within the loop.

Sub SaveAsInLoop()
Dim SheetNumber As Long

For SheetNumber = 1 To ThisWorkbook.Sheets.Count
    Debug.Print Sheets("Sheet" & SheetNumber).Name
Next SheetNumber

End Sub

Save multiple sheets that contain name “sheet…” as individual workbooks

1 Answers