SOLVED

move or copy multiple tabs to individual tabs

Occasional Contributor

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

3 Replies

@Sherienlhy 

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

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?

best response confirmed by Sherienlhy (Occasional Contributor)
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