Excel VBA copy each worksheet to new workbook

This Excel tutorial explains how to copy each worksheet to new workbook in Excel VBA.

You may also want to read:

VBA Worksheets.Copy Method to copy worksheet

VBA copy workbook or file

Excel VBA copy each worksheet to new workbook

Suppose you have a workbook that contains multiple worksheets, now you want to copy each worksheet to a new workbook. In this post, I will demonstrate how to do it in Macro.

Sub cpyWS()
    Application.ScreenUpdating = False
     Application.DisplayAlerts = False
    For Each ws In ThisWorkbook.Sheets
        Set newbook = Workbooks.Add
        ws.Copy After:=newbook.Sheets(newbook.Sheets.Count)
        newbook.Sheets(ws.Name).Cells.Copy
        newbook.Sheets(ws.Name).Cells.PasteSpecial Paste:=xlPasteValues
        newbook.Sheets("Sheet1").Delete
        newbook.SaveAs "C:\test\" & ws.Name & ".xlsx"   'define the path where you want to save the new workbook
        newbook.Close
    Next ws
     Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

In this Macro, I copy each worksheet in current workbook to a new workbook and save the file name as worksheet name. Note that I copy and paste all contents as value in case formulas are broken. You can delete the codes in red if needed.

Example – copy each worksheet to new workbook

Suppose we have a workbook that contains two worksheets: SheetA and SheetB.

       

Save the Macro in current workbook, then run the Macro.

In C:\test\ folder    two files are generated using the worksheet names.

 

 

 

Open SheetA.xlsx to check the contents

 

Open SheetB.xlsx