Learn

Macro 24: Create a New Workbook for Each Worksheet

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 InsertModule.

4. Type or paste the code in the newly created module.

learn
We will be happy to hear your thoughts

Leave a reply

Share knowledge
Learn
Logo
Enable registration in settings - general