[Excel VBA] How to Create a “Duplicate” Button to Copy Sheets Easily

2025年12月29日

Background

When working in Excel, you often need to copy and reuse existing sheets. Previously, I would manually right-click the sheet tab and select “Move or Copy,” but this process felt tedious. I wanted to automate it using VBA.

In this article, I will share a macro that allows you to duplicate the current sheet simply by clicking a button on the Excel sheet.

Desired Specifications

Place a “Duplicate” button on the Excel sheet.
When the button is clicked, a dialog box appears asking for a new sheet name.
The current sheet is duplicated to the beginning of the workbook using the entered name.
If the sheet name matches an existing one, the error is ignored.

Implementation Code (VBA Macro)

Below is the macro code.

Sub DuplicateSheetAnd()
    Dim newSheetName As String
    Dim newSheet As Worksheet

    ' Ask for the new sheet name via a dialog box
    newSheetName = Application.InputBox("Please enter a new sheet name:", "Input Sheet Name", Type:=2)

    ' Exit the process if input is canceled
    If newSheetName = "False" Then Exit Sub

    ' Duplicate the current sheet to the beginning and set it to a variable
    ActiveSheet.Copy Before:=ThisWorkbook.Sheets(1)
    Set newSheet = ActiveSheet

    ' Set the name for the new sheet
    On Error Resume Next ' Ignore error if a sheet with the same name already exists
    newSheet.Name = newSheetName
    On Error GoTo 0 ' Reset error handling
End Sub

This macro copies the currently active sheet, places it at the beginning of the workbook, and renames it to the name you specified.

How to Set Up the Button (Shape Button)

To use this macro, follow these steps to create a button:

Go to Insert > Shapes in the Excel ribbon.
Place your preferred shape on the worksheet.
Right-click the shape, select Assign Macro, and choose DuplicateSheetAnd.

Now, when you click the shape button, the macro will run.

Summary

Reduces mouse clicks and allows sheet duplication with one click.
Offers flexibility by allowing you to input a new sheet name every time.
Easy to implement, even for VBA beginners.

This simple mechanism is highly useful for mass-producing daily reports or managing business templates. Please try it out to improve your work efficiency.