You can easily copy sheets in Excel manually with a few simple mouse clicks. On the other hand, you need a macro if you want to automate this process. In this guide, we're going to show you how to copy sheets in Excel with VBA.
Before you start
If you are new to VBA and macro concept, VBA is a programming language for Office products. Microsoft allows users to automate tasks or modify properties of Office software. A macro, on the other hand, is a set of VBA code which you tell the machine what needs to be done.
Macros, or codes, should be written in modules, which are text areas in VBA’s dedicated user interface. Also, the file should be saved as Excel Macro Enabled Workbook in XLSM format to keep the codes.
You can find detailed instructions in our How to create a macro in Excel guide.
Copy active sheet to a new workbook
The first code is the simplest and shortest one which performs the action the title suggests:
Public Sub CopyActiveSheetToNewWorkbook() ActiveSheet.Copy End Sub
As you can figure out ActiveSheet selector indicates the active sheet in the user window. Once the code run successfully, you will see the copy in a new workbook.
Copy a specific sheet to a new workbook
The following code copies “SUMIFS” sheet into a new workbook, regardless of sheet’s active status.
Public Sub CopySpecificSheetToNewWorkbook() Sheets("SUMIFS").Copy End Sub
Copy selected sheets to a new workbook
If you need to copy selected sheets into a new workbook, use ActiveWindow.SelectedSheets selector.
Public Sub CopyActiveSheetsToNewWorkbook() ActiveWindow.SelectedSheets.Copy End Sub
Copy active sheet to a specific position in the same workbook
If you specify a position in the code, VBA duplicates the sheet in a specific position of in the workbook. To do this placement, you can use Before and After arguments with Copy command. With these arguments, you can place the new sheet before or after an existing worksheet.
You can use either sheet names or their indexes to indicate the existing sheet. Here are a few samples:
Public Sub CopyActiveSheetAfterSheet_Name() 'Copies the active sheet after "Types" sheet ActiveSheet.Copy After:=Sheets("Types") End Sub Public Sub CopyActiveSheetAfterSheet_Index() 'Copies after 2nd sheet ActiveSheet.Copy After:=Sheets(2) End Sub Public Sub CopyActiveSheetAfterLastSheet() 'Copies the active sheet after the last sheet 'Sheets.Count command returns the number of the sheets in the workbook ActiveSheet.Copy After:=Sheets(Sheets.Count) End Sub Public Sub CopyActiveSheetBeforeSheet_Name() 'Copies the active sheet before "Types" sheet ActiveSheet.Copy Before:=Sheets("Types") End Sub Public Sub CopyActiveSheetBeforeSheet_Index() 'Copies the active sheet before 2nd sheet ActiveSheet.Copy Before:=Sheets(2) End Sub Public Sub CopyActiveSheetBeforeFirstSheet() 'Copies the active sheet before the first sheet ActiveSheet.Copy Before:=Sheets(1) End Sub
Copy active sheet to an existing workbook
To copy anything to an existing workbook, there are 2 perquisites:
- Target workbook should be open as well
- You need to specify the target workbooks by name
Sub CopySpecificSheetToExistingWorkbook() ' define a workbook variable and assign target workbook ' thus, we can use variable multiple times instead of workbook reference Dim targetSheet As Workbook Set targetSheet = Workbooks("Target Workbook.xlsx") 'copies "Names" sheet to the last position in the target workbook Sheets("Names").Copy After:=targetSheet.Sheets(targetSheet.Worksheets.Count) End Sub