SOLVED

move or copy multiple tabs to individual tabs

%3CLINGO-SUB%20id%3D%22lingo-sub-2540935%22%20slang%3D%22en-US%22%3Emove%20and%20copy%20multiple%20tabs%20to%20individual%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2540935%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20we%26nbsp%3Bmove%20and%20copy%20multiple%20tabs%20to%20individual%20excel%20template%20in%201%20go%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2540935%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2541590%22%20slang%3D%22en-US%22%3ERe%3A%20move%20or%20copy%20multiple%20tabs%20to%20individual%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2541590%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1101851%22%20target%3D%22_blank%22%3E%40Sherienlhy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20want%20to%20move%2Fcopy%20each%20sheet%20to%20a%20separate%20workbook%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2541763%22%20slang%3D%22en-US%22%3ERe%3A%20move%20or%20copy%20multiple%20tabs%20to%20individual%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2541763%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1101851%22%20target%3D%22_blank%22%3E%40Sherienlhy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20run%20the%20following%20macro%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopySheets()%0A%20%20%20%20Dim%20sPath%20As%20String%0A%20%20%20%20Dim%20wsh%20As%20Worksheet%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20'%20You%20can%20specify%20another%20path%0A%20%20%20%20sPath%20%3D%20ThisWorkbook.Path%20%26amp%3B%20%22%5C%22%0A%20%20%20%20'%20Loop%20through%20the%20worksheets%0A%20%20%20%20For%20Each%20wsh%20In%20Worksheets%0A%20%20%20%20%20%20%20%20'%20Copy%20sheet%20to%20new%20workbook%0A%20%20%20%20%20%20%20%20wsh.Copy%0A%20%20%20%20%20%20%20%20'%20Save%20and%20close%20it%0A%20%20%20%20%20%20%20%20ActiveWorkbook.Close%20SaveChanges%3A%3DTrue%2C%20Filename%3A%3DsPath%20%26amp%3B%20wsh.Name%20%26amp%3B%20%22.xlsx%22%0A%20%20%20%20Next%20wsh%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
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