5 Proven VBA Methods to Copy Worksheets Instantly

Visual Basic for Applications (VBA) provides powerful tools that can automate repetitive tasks in Microsoft Excel. One of the most common automation tasks is copying worksheets within or between workbooks. Here, we explore five robust VBA methods to instantly copy worksheets, enhancing your efficiency with Excel.

Method 1: Simple Worksheet Copy

The simplest way to copy a worksheet is by using the Worksheet.Copy method. Here’s how:

• Open the Visual Basic Editor (VBE) with Alt + F11.
• Insert a new module by selecting Insert > Module.
• Enter the following VBA code:

Sub SimpleCopySheet()
    ThisWorkbook.Worksheets(“Sheet1”).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End Sub

This code copies “Sheet1” to the end of the current workbook.

Method 2: Copy Worksheet with Custom Name

To copy a worksheet and give it a new name:

• Follow the steps from Method 1.
• Use this code:

Sub CopyWithNewName()
    With ThisWorkbook
        .Worksheets(“Sheet1”).Copy After:=.Sheets(.Sheets.Count)
        ActiveSheet.Name = “NewSheetName”
    End With
End Sub

Method 3: Copying to Another Workbook

If you need to copy a sheet to another workbook, here’s how:

• Open or create another Excel workbook.
• In VBE, insert this code:

Sub CopyToAnotherWorkbook()
    Dim sourceWorkbook As Workbook
    Dim destinationWorkbook As Workbook

Set sourceWorkbook = ThisWorkbook
Set destinationWorkbook = Workbooks.Open("C:\Path\To\Workbook.xlsx")

sourceWorkbook.Worksheets("Sheet1").Copy After:=destinationWorkbook.Sheets(destinationWorkbook.Sheets.Count)
destinationWorkbook.Save
destinationWorkbook.Close


End Sub

🚨 Note: Ensure the destination workbook is already open or modify the code to open it automatically.

Method 4: Copying Multiple Sheets at Once

VBA can also handle multiple sheets at once:

• Enter this code into the VBE:

Sub CopyMultipleSheets()
    Dim sheet As Worksheet
    Dim wbSource As Workbook
    Dim wbDestination As Workbook
    Set wbSource = ThisWorkbook
    Set wbDestination = Workbooks.Add

For Each sheet In wbSource.Worksheets
    If sheet.Name Like "*Template*" Then
        sheet.Copy After:=wbDestination.Sheets(wbDestination.Sheets.Count)
    End If
Next sheet
wbDestination.SaveAs "C:\NewWorkbookWithTemplates.xlsx"
wbDestination.Close


End Sub

Method 5: Advanced Worksheet Cloning

For more complex scenarios, you might need to clone a worksheet, including:

• Formulas, data, formats, charts, tables, and comments.
• Use this comprehensive VBA script:

Sub CloneWorksheet()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim wb As Workbook
    Set wb = ThisWorkbook

Set wsSource = wb.Sheets("Sheet1")
wsSource.Copy After:=wb.Sheets(wb.Sheets.Count)
Set wsTarget = wb.Sheets(wb.Sheets.Count)

With wsTarget
    .Name = "ClonedSheet"
    .Range(wsSource.UsedRange.Address).Formula = wsSource.UsedRange.Formula
    .Range(wsSource.UsedRange.Address).Value = wsSource.UsedRange.Value
    .Range(wsSource.UsedRange.Address).NumberFormat = wsSource.UsedRange.NumberFormat
    .Range(wsSource.UsedRange.Address).FormatConditions.Add
    .Tab.Color = wsSource.Tab.Color
    'Copy chart
    Dim obj As OLEObject
    For Each obj In wsSource.ChartObjects
        obj.Copy
        .Paste
    Next obj
End With


End Sub

The versatility of VBA in Excel allows for quick and efficient automation of worksheet copying, whether within the same workbook, to different workbooks, or for complex cloning tasks. By implementing these VBA methods, you can significantly boost productivity, minimize manual errors, and streamline your data management process. Through understanding and applying these techniques, your workflow with Excel will become more seamless, enabling you to focus on more strategic tasks rather than repetitive manual operations.

What happens to formulas when copying sheets?

+

When you copy a worksheet, Excel preserves the formulas, but relative references in formulas adjust based on the new position of the copied sheet. If you want to keep the formulas as they are, you need to handle this manually or use methods that specifically address formula preservation.

Can I undo a sheet copy operation?

+

Excel’s Undo functionality (Ctrl + Z) does not work for VBA-driven changes. To revert changes, you must manually delete the copied sheet or keep a backup of your workbook before running VBA scripts.

How do I avoid naming conflicts when copying sheets?

+

Use methods that check for existing sheet names before copying. If a name conflict is detected, append a number or use a unique naming convention. The code provided in Method 2 demonstrates one way to handle this issue.