Hello Excellers, time for some #macromonday fun. Today let’s take a look at a creating an Excel VBA macro as a really easy way to export each worksheet in your Excel workbook as a new workbook.

So let’s say that if you have a workbook with multiple tabs, and you need to save one, two or even all of the worksheets as separate workbooks. Well, there is no need to do any copying, paste or deleting. We can write nice neat little Excel Macro to do the hard work for us

What Does The Macro Do?

This Macro will export all of the visible worksheets, into new workbooks. Excel then names the newly created workbooks and saves them in the same location as the original.

How Does The Macro Work?

First, we need to create a new module to save this Macro into. I have chosen to store this in my Personal Macro Workbook so I can use with any workbook I want to. Saving it in this location gives me access to the macro so I can choose to run it for any workbook where I want to export and save each worksheet as a new workbook. If you want to read more about the Personal Macro workbook you can check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Step 1. We need to declare any variables. In this macro, it is just the one, ws As Worksheet. This simply means that Excel allocates some memory to enable it to store this information and represent this value.

Step 2. I have chosen to turn off screen updating. Unfortunately, we will not be able to see what the code is doing, but it does help to speed up the code

Step 3. Excel begins looping through the worksheets in the workbook

Step 4. Excel copies any visible worksheets. Hidden worksheets are NOT copied.

Step 5. The worksheets are saved in the same location or directory as the original workbook with the sheet name.

Step 6. Excel loops back to next worksheet.

Final Step 7. Screen updating is turned back on and the macro ends once all of the worksheets have been copied

Finally, there is my favourite step. Lets’ test our VBA code.

Next Steps. Do you want to copy the code?

[stextbox id=”grey”]

Sub CopyWorkbooks()
‘macro by HowToExcelAtExcel.Com
‘1. Declare our variables
Dim ws As Worksheet

‘Step 2: Turn screen updating off- to help speed up the code
Application.ScreenUpdating = False

‘Step 3. Begin looping through each of the worksheets in the workbook
For Each ws In ThisWorkbook.Worksheets

‘Step 4. Copy each of the visible worksheets
ws.Copy

‘Step 5: Save the new workbook with sheet name.
ActiveWorkbook.SaveAs ThisWorkbook.Path & “\” & ws.Name
ActiveWorkbook.Close SaveChanges:=True

‘Step 6: Loop back around to the next worksheet
Next ws

‘Step 7: Turn screen updating on
Application.ScreenUpdating = True

End Sub

[/stextbox]

You can check out my full list of Macro Mondays blog posts, and the code is always free for you to copy at the end of the blog post.

If you want more tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Next Steps?. Do You Want To Learn How Write Excel Macros? Check Out Chandoo’s VBA Classes.