Forum Discussion

Sherienlhy's avatar
Sherienlhy
Copper Contributor
Jul 13, 2021
Solved

move or copy multiple tabs to individual tabs

Can we move or copy multiple tabs to individual excel template in 1 go?
excel
  • HansVogelaar's avatar
    HansVogelaar
    Jul 13, 2021

    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
  • Sherienlhy's avatar
    Sherienlhy
    Copper Contributor
    Jul 13, 2021

    Hi HansVogelaar - 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?

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 13, 2021

      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

Resources