top of page

Create separate workbook from worksheets VBA

Split worksheets into separate files

Macro to create multiple workbooks from a master worksheet

Recently as part of a consulting assignment I had a task to create separate workbook from all the existing worksheets in the file. I wrote the below code for the purpose. May be if you have a similar requirement you can use this code with little or no amendment.

Sub makefiles()

Dim wb As Workbook, wbnew As Workbook

'creating variables to store workbook

Dim st As Worksheet

'creating variable to store a worksheet

Set wb = ThisWorkbook

Application.DisplayAlerts = False

'when a sheet is deleted, excel throws an alert; the above code is to skip such alerts; if not it will throw a dialog box

For Each st In ActiveWorkbook.Sheets

'Loop to repeat a set of commands

If st.Name <> "Summary" Then

'My requirement was such that I didnt want a workbook created for sheet Summary thus the above code


'adds a new workbook

ActiveSheet.Name = "Rough"

'new workbook has only 1 sheet; renaming it so that it can be deleted easily; optional you can use sheet1 instead

Set wbnew = ActiveWorkbook



st.Copy before:=wbnew.Sheets(1)

'this code copies the sheet and moves it to the new workbook




wbnew.SaveAs wb.Path & "\" & st.Name & ".xlsx"

'save the new workbook in the same folder as the master file


'As always drop an email to for any excel or VBA related work

'Or visit us at to watch VBA videos


End If

Next st

End Sub


bottom of page