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

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

move or copy multiple tabs to individual tabs

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

3 Replies

HansVogelaar

MVP

Jul 13, 2021

Sherienlhy

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

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

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

Forum Discussion

move or copy multiple tabs to individual tabs

3 Replies

Resources