How to duplicate a sheet in Excel with VBA

This tutorial offers a suite of Excel macros for efficient sheet duplication: copying and renaming based on cell values, copying multiple sheets simultaneously, transferring active worksheets to other files without opening them, and more. Manually copying sheets is simple enough for a few sheets, but becomes tedious when dealing with numerous sheets repeatedly. These macros automate the process.

• Copy sheet to a new workbook
• Duplicate multiple sheets
• Copy sheet to another Excel file
• Copy and rename a sheet
• Copy and rename a sheet based on cell value
• Copy a worksheet to a closed workbook
• Copy a sheet from another workbook without opening it
• Duplicate a sheet multiple times
• Copying sheets in Excel using VBA

Excel VBA Macro: Copying a Sheet to a New Workbook

This concise macro copies the active sheet to a new workbook.

Public Sub CopySheetToNewWorkbook()
    ActiveSheet.Copy
End Sub

Excel VBA Macro: Copying Multiple Sheets

Select the desired worksheets and run this macro to copy them to a new workbook.

Public Sub CopySelectedSheets()
    ActiveWindow.SelectedSheets.Copy
End Sub

Excel VBA Macro: Copying a Sheet to Another Workbook

These macros copy the active sheet to another workbook, offering options for placement:

Copying to the beginning of another workbook: This macro inserts the copied sheet before the first sheet in the destination workbook ("Book1.xlsx" – replace with your file path).

Public Sub CopySheetToBeginningAnotherWorkbook()
    ActiveSheet.Copy Before:=Workbooks("Book1.xlsx").Sheets(1)
End Sub

Copying to the end of another workbook: This macro appends the copied sheet to the end of the destination workbook ("Book1.xlsx" – replace with your file path).

Public Sub CopySheetToEndAnotherWorkbook()
    ActiveSheet.Copy After:=Workbooks("Book1.xlsx").Sheets(Workbooks("Book1.xlsx").Worksheets.Count)
End Sub

Note: The target workbook must exist.

Copying to a selected workbook: This utilizes a UserForm (UserForm1) with a ListBox (ListBox1) to select the destination workbook from open workbooks. Two buttons control selection and closing.

The UserForm code:

Public SelectedWorkbook As String

Private Sub UserForm_Initialize()
    SelectedWorkbook = ""
    ListBox1.Clear
    For Each wbk In Application.Workbooks
        ListBox1.AddItem (wbk.Name)
    Next
End Sub

Private Sub CommandButton1_Click()
    If ListBox1.ListIndex > -1 Then
        SelectedWorkbook = ListBox1.List(ListBox1.ListIndex)
    End If
    Me.Hide
End Sub

Private Sub CommandButton2_Click()
    SelectedWorkbook = ""
    Me.Hide
End Sub

Macros to use with the UserForm:

Copy to the beginning of the selected workbook:

Public Sub CopySheetToBeginningSelectedWorkbook()
    Load UserForm1
    UserForm1.Show
    If (UserForm1.SelectedWorkbook  "") Then
        ActiveSheet.Copy Before:=Workbooks(UserForm1.SelectedWorkbook).Sheets(1)
    End If
    Unload UserForm1
End Sub

Copy to the end of the selected workbook:

Public Sub CopySheetToEndSelectedWorkbook()
    Load UserForm1
    UserForm1.Show
    If (UserForm1.SelectedWorkbook  "") Then
        ActiveSheet.Copy After:=Workbooks(UserForm1.SelectedWorkbook).Sheets(Workbooks(UserForm1.SelectedWorkbook).Worksheets.Count)
    End If
    Unload UserForm1
End Sub

The macro will display a list of open workbooks for selection.

Excel Macro: Copying and Renaming a Sheet

These macros automate sheet renaming after copying:

This macro copies the active sheet, names it "Test Sheet" (customizable), and places it at the end.

Public Sub CopySheetAndRenamePredefined()
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    On Error Resume Next
    ActiveSheet.Name = "Test Sheet"
End Sub

This macro prompts the user for a custom sheet name.

Public Sub CopySheetAndRename()
    Dim newName As String
    On Error Resume Next
    newName = InputBox("Enter the name for the copied worksheet")
    If newName  "" Then
        ActiveSheet.Copy After:=Worksheets(Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = newName
    End If
End Sub

The macro displays an input box for name entry.

Excel Macro: Copying and Renaming Based on Cell Value

These macros rename the copied sheet using a cell's value:

This macro uses the currently selected cell's value for the new sheet name.

Public Sub CopySheetAndRenameByCell()
    Dim newName As String
    On Error Resume Next
    newName = InputBox("Enter the name for the copied worksheet", "Copy worksheet", ActiveCell.Value)
    If newName  "" Then
        ActiveSheet.Copy After:=Worksheets(Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = newName
    End If
End Sub

This macro uses the value of cell A1 (changeable) for the new sheet name.

Public Sub CopySheetAndRenameByCell2()
    Dim wks As Worksheet
    Set wks = ActiveSheet
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    If wks.Range("A1").Value  "" Then
        On Error Resume Next
        ActiveSheet.Name = wks.Range("A1").Value
    End If
    wks.Activate
End Sub

Excel Macro: Copying to a Closed Workbook

This macro copies the active sheet to a closed workbook selected via a file dialog.

Public Sub CopySheetToClosedWorkbook()
    Dim fileName
    Dim closedBook As Workbook
    Dim currentSheet As Worksheet
    fileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
    If fileName  False Then
        Application.ScreenUpdating = False
        Set currentSheet = Application.ActiveSheet
        Set closedBook = Workbooks.Open(fileName)
        currentSheet.Copy After:=closedBook.Sheets(closedBook.Worksheets.Count)
        closedBook.Close (True)
        Application.ScreenUpdating = True
    End If
End Sub

Excel VBA Macro: Copying from a Closed Workbook

This macro copies a sheet from a specified closed workbook (update path and sheet name).

Public Sub CopySheetFromClosedWorkbook()
    Dim sourceBook As Workbook
    Application.ScreenUpdating = False
    Set sourceBook = Workbooks.Open("C:\\Users\\XXX\\Documents\\Target_Book.xlsx") 'Update path
    sourceBook.Sheets("Sheet1").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 'Update sheet name
    sourceBook.Close
    Application.ScreenUpdating = True
End Sub

Excel VBA Macro: Duplicating a Sheet Multiple Times

This macro creates multiple copies of the active sheet.

Public Sub DuplicateSheetMultipleTimes()
    Dim n As Integer
    On Error Resume Next
    n = InputBox("How many copies of the active sheet do you want to make?")
    If n >= 1 Then
        For numtimes = 1 To n
            ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
        Next
    End If
End Sub

The macro displays an input box for the number of copies.

Adding Macros to Your Workbook:

1. Open the Excel workbook.
2. Press Alt F11 to open the VBA editor.
3. Right-click "ThisWorkbook," select "Insert" > "Module."
4. Paste the macro code into the module.
5. Press F5 to run.

Running Macros from a Sample Workbook: (Download a sample workbook containing these macros). Open the sample workbook, then in your own workbook, press Alt F8, select the macro, and click "Run."

The above is the detailed content of How to duplicate a sheet in Excel with VBA. For more information, please follow other related articles on the PHP Chinese website!