move or copy multiple tabs to individual tabs

[Sherienlhy]

Can we move or copy multiple tabs to individual excel template in 1 go?

[#2]

@Sherienlhy 

Do you want to move/copy each sheet to a separate workbook?

[#3]

Hi @Hans Vogelaar - Yes, do we have the way to do it which no need to move or copy each sheet / excel tabs 1 by 1 to new separate workbook?

[#4 · Accepted solution]

@Sherienlhy 

You could run the following macro:

Sub CopySheets()
    Dim sPath As String
    Dim wsh As Worksheet
    Application.ScreenUpdating = False
    ' You can specify another path
    sPath = ThisWorkbook.Path & "\"
    ' Loop through the worksheets
    For Each wsh In Worksheets
        ' Copy sheet to new workbook
        wsh.Copy
        ' Save and close it
        ActiveWorkbook.Close SaveChanges:=True, Filename:=sPath & wsh.Name & ".xlsx"
    Next wsh
    Application.ScreenUpdating = True
End Sub