This Excel tutorial explains how to copy each worksheet to new workbook in Excel VBA.
You may also want to read:
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