Hello, Excellers. Welcome back to more Excel VBA in today’s #MacroMonday blog post in my 2020 series. I will show you how to write a really simple Excel macro that will copy the active worksheet to a new Excel workbook. This is really useful if you need to take just one worksheet out of your workbook to create a new workbook.
Manually Copy A Worksheet To An New Excel Workbook.
If you want to manually carry out the same procedure then feel free to read my corresponding blog post and YouTube video. The links are below.
Starting The VBA Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11 or
- Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
Watch The Video On Enabling The Developer Tab.
Before we begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook.
What Is The Difference In These Locations?.
Well, if you save the code in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook. When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location, by default this macro workbook is named Personal.xlsb. This is a very useful macro which can be used over and over again. I therefore still want to save it in my personal macro workbook.
If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
I want to use this code over and over again so I will choose at this time to save it in my Personal Macro Workbook.
Preparing To Write The Code To Copy The Active Worksheet.
First, I need to start off the process by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro.
In this example, I have called it CopyMyWorksheet. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines of code
[stextbox id=’info’]
Sub CopyMyWorksheet()
End Sub [/stextbox]
Use The Worksheet.Copy Method
We now use the Worksheet.Copy method in this code to copy the active worksheet. This Copies the sheet to another location in the current workbook or a new workbook.
The syntax of this method is as as follows:
Syntax
expression.Copy (Before, After)
expression A variable that represents a Worksheet object.
The optional parameters of Before and After. These represent the sheet before which the copied sheet will be placed. You cannot specify Before if you specify After. The After parameter is the sheet after which the copied sheet will be placed. You cannot specify After if you specify Before.
If you choose to use no parameters as in my example macro today then only the worksheet will be copied to a new Excel workbook. the workbook will contain only that one worksheet. Perfect. This is exactly what I want today.
[stextbox id=’info’]
ActiveSheet.Copy
[/stextbox]
Yes this simple one line of code does all of the work for us. The single worksheet retains the Name and CodeName properties of the source worksheet. If the copied worksheet held a worksheet code sheet in a VBA project, that is also carried into the new workbook. Handy right?.
Ending The Macro.
Once the worksheet has been copied the code ends with the End Sub bit of code. This was already input by default by Excel when we began writing the macro.
[stextbox id=’info’]
End Sub
[/stextbox]
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.