Macro 24: Create a New Workbook for Each Worksheet
Many Excel analysts need to parse their workbooks into separate books per worksheet tab. In other
words, they need to create a new workbook for each of the worksheets in their existing workbook.
You can imagine what an ordeal this would be if you were to do it manually. The following macro
helps automate that task.
How it works
In this macro, you are looping the worksheets, copying each sheet, and then sending the copy to a
new workbook that is created on the fly. The thing to note here is that the newly created workbooks
are being saved in the same directory as your original workbook, with the same filename as the
copied sheet (wb.SaveAs ThisWorkbook.Path & “\” & ws.Name).
Sub Macro24()
‘Step 1: Declare all the variables.
Dim ws As Worksheet
Dim wb As Workbook
‘Step 2: Start the looping through sheets
For Each ws In ThisWorkbook.Worksheets
‘Step 3: Create new workbook and save it.
Set wb = Workbooks.Add
wb.SaveAs ThisWorkbook.Path & “\” & ws.Name
‘Step 4: Copy the target sheet to the new workbook
ws.Copy Before:=wb.Worksheets(1)
wb.Close SaveChanges:=True
‘Step 5: Loop back around to the next worksheet
Next ws
End Sub
As you’re running this macro, naming the newly created files to match the sheet name may
cause an error. For instance, the macro throws an error when creating a new file from a sheet
called May| Revenue (because of the pipe character). To make a long story short, avoid
naming your worksheets with these restricted characters.
1. Step 1 declares two object variables. The ws variable creates a memory container for each
worksheet the macro loops through. The wb variable creates the container for the new
workbooks we create.
2. In Step 2, the macro starts looping through the sheets. The use of the ThisWorkbook
object ensures that the active sheet that is being copied is from the workbook the code
is in, not the new workbook that is created.
3. In Step 3, we create the new workbook and save it. We save this new book in the same path
as the original workbook (ThisWorkbook). The filename is set to be the same name as the
currently active sheet.
4. Step 4 copies the currently active sheet and uses the Before parameter to send it to
the new book as the first tab.
5. Step 5 loops back to get the next sheet. After all of the sheets are evaluated, the
macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
