Hello, Excellers. Welcome back to another #Excel #MacroMonday blog post in my Excel 2021 series. Today, I will show you how to easily copy the active worksheet to a new Excel worksheet with a small bit of Excel VBA macro Code. This is a real timesaver. With one click you can easily make a copy of your Excel worksheet you are working on.

Read All of my Macro Monday Blog Posts

Prepare To Write The Macro.

First, open the Visual Basic Editor.  There are two ways to do this.  Hit ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic.  Both methods have the same result.   

If you cannot see the Developer Tab you may need to enable it. Check out my very short YouTube video for instructions. I will show you how to enable the developer tab in just a few minutes.

Starting The Macro.

After the new module is inserted, type Sub then the name of the Excel macro. In this example, I have named my Macro CopyActiveSheet().

[stextbox id=’info’]
Sub CopyActiveSheet()
End Sub
[/stextbox]

Workbook.ActiveSheet Property.

For the next line of code, we use Workbook.ActiveSheet Property. This returns a worksheet object that represents the active sheet. Or the sheet on top in the active workbook or specified workbook. The code returns Nothing if no sheet is active.

[stextbox id=’info’]
ThisWorkbook.ActiveSheet.Copy _
[/stextbox]

Create A New Excel Workbook. Copy The Active Worksheet.

This next line of code creates a new Excel workbook. It adds the copied worksheet before any worksheets in the new Excel workbook. How easy and cool is that?.

[stextbox id=’info’]
Before:=Workbooks.Add.Worksheets(1)
[/stextbox]

Ending The Macro.

Once the active worksheet has been copied to a new Excel workbook the code ends. This line of code was already inserted by Excel as we entered the name of the macro in the first line of code.

[stextbox id=’info’]
End Sub
[/stextbox]

Final Part- Testing The Macro. Copy The Active Worksheet.

Finally, let’s go ahead and work through an example and test the macro. I need to copy my Monthly Data Worksheet in my Sales data Excel workbook. So, this is the active worksheet I want to copy to my new Excel workbook.

Next, we need to run the Excel macro. Do this by

• Developer Tab
• Macros or hit ALT +F8
• Select your named macro

• Hit Run
• With a single click of the mouse I now have a new workbook complete with my Sales Data.

How easy and quick was that?. Here is the full code to copy.

[stextbox id=’info’]

Sub CopyActiveSheet()
‘macro by www.howtoexcelatexcel.com
ThisWorkbook.ActiveSheet.Copy _
Before:=Workbooks.Add.Worksheets(1)
End Sub

[/stextbox]