[Excel VBA] How to Copy Existing Worksheets (.Copy)

Introduction

In tasks like creating monthly reports, you often need to duplicate a “Template” sheet that already contains the correct formatting and formulas. While copying a sheet manually is simple, using VBA allows you to completely automate the process.

This article explains the workflow using the Worksheet.Copy method: copying a template sheet, placing it in a specific position, and renaming it.

The Basics of the Worksheet.Copy Method

The .Copy method is the command used to duplicate a specified sheet.

SheetToCopy.Copy [Before], [After]

• Before Argument: Places the copy before (to the left of) the specified sheet.
• After Argument: Places the copy after (to the right of) the specified sheet.

Note: You can specify either Before or After, but not both at the same time.

Practical Code Examples

Here are two common patterns used in actual business scenarios.

1. Copying After a Specific Sheet

In this example, we copy the “Template” sheet and place it immediately after the “August” sheet, then rename it to “September”.

Sub CopySheetAfter()

    ' Declare variables
    Dim referenceSheet As Worksheet
    Dim templateSheet As Worksheet
    
    ' Set the reference sheet and the template sheet to copy
    Set referenceSheet = ThisWorkbook.Worksheets("August")
    Set templateSheet = ThisWorkbook.Worksheets("Template")
    
    ' Copy the "Template" sheet and place it AFTER the "August" sheet
    templateSheet.Copy After:=referenceSheet
    
    ' Rename the newly created sheet
    ' (referenceSheet.Next refers to the sheet immediately to the right)
    referenceSheet.Next.Name = "September"
    
    MsgBox "Sheet copied and renamed successfully."

End Sub

Tip: The .Next property refers to the sheet immediately to the right of the specified object. This is a smart way to identify the newly created sheet when using After.

2. Copying to the Beginning of the Workbook

In this example, we copy the “Template” sheet to the far left (beginning) of the workbook and rename it.

Sub CopySheetToBeginning()

    ' Copy "Template" BEFORE the first sheet in the workbook
    ThisWorkbook.Worksheets("Template").Copy Before:=ThisWorkbook.Worksheets(1)
    
    ' When the .Copy method runs, the new sheet automatically becomes active.
    ' Therefore, we can use ActiveSheet to rename it.
    ActiveSheet.Name = "LatestSummary"
    
    MsgBox "Sheet copied to the beginning and renamed."

End Sub

Key Points

How to Reference the Copied Sheet

To manipulate the new sheet via VBA (e.g., to rename it), you need a way to reference it immediately after copying.

• Method A: Use .Next or .Previous If you used After:=mySheet, you can use mySheet.Next to refer to the new sheet. If you used Before, you can use .Previous. This is useful when the relative position is clear.
• Method B: Use ActiveSheet When .Copy is executed, the newly created sheet always becomes the active sheet. You can rely on this behavior and simply use ActiveSheet immediately after the copy command. This is a very common and versatile pattern.

Caution: Omitting Arguments

If you omit both Before and After arguments (e.g., writing only Worksheets("Template").Copy), the sheet will be copied into a new, separate workbook.

This is a useful feature when you want to export a sheet to a new file, but be careful not to do this accidentally if you intend to keep the sheet within the same workbook.

Summary

The basics of copying worksheets in VBA are:

1. Use the .Copy method and specify the position using Before or After.
2. If arguments are omitted, the sheet is copied to a new workbook.
3. The copied sheet becomes the ActiveSheet immediately, which is the easiest way to reference it for renaming.

Mastering the .Copy method allows you to dramatically improve the efficiency of routine tasks, such as auto-generating monthly reports or mass-producing analysis sheets.