Macro 23: Copy a Worksheet to a New Workbook
In Excel, you can manually copy an entire sheet to a new workbook by right-clicking the target sheet
and selecting the Move or Copy option. Unfortunately, if you try to record a macro while you do this,
the macro recorder fails to accurately write the code to reflect the task. So if you need to program-
matically copy an entire sheet to a brand new workbook, this macro delivers.
How it works
In this macro, the active sheet is first being copied. Then we use the Before parameter to send the
copy to a new workbook that is created on the fly. The copied sheet is positioned as the first sheet in
the new workbook.
The use of the ThisWorkbook object is important here. This ensures that the active sheet that is
being copied is from the workbook that the code is in, not the new workbook that is created.
Sub Macro23()
‘Copy sheet, and send to new workbook
ThisWorkbook.ActiveSheet.Copy _
Before:=Workbooks.Add.Worksheets(1)
End Sub
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.
