How to duplicate sheet in Excel with VBA

The tutorial provides a collection of macros to duplicate sheets in Excel: copy and rename based on cell value, copy multiple sheets, copy an active worksheet to another file without opening it, and more.

Manually copying sheets in Excel is pretty quick and straightforward... if performed just once or twice. Duplicating multiple sheets multiple times is boring and time consuming. On this page, you will find a handful of useful macros to automate this task.

Excel VBA to copy sheet to new workbook

This simplest one-line macro does exactly what its name suggests - copies the active sheet to a new workbook.

Public Sub CopySheetToNewWorkbook() activeSheet.Copy End Sub

Copy multiple sheets in Excel with VBA

If you'd like to copy several sheets from the active workbook to a new one, select all the worksheets of interest and run this macro:

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

Excel VBA to copy sheet to another workbook

Depending on where you want to insert the copied sheet, use one of the following macros.

Copy sheet to the beginning of another workbook

This macro copies the active sheet before all other worksheets in the destination file, Book1 in this example. To copy to another file, replace "Book1.xlsx" with the full name of your target workbook.

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

Copy sheet to the end of another workbook

This piece of code duplicates the active worksheet and places the copy to the end of Book1. Again, please remember to replace "Book1.xlsx" with the name of your destination workbook.

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

Note. For the macros to work, the target workbook must be saved on your hard drive or network.

Copy sheet to a selected workbook

To be able to copy the current sheet to any open workbook, you can create a UserForm (named UserForm1) with a ListBox control (named ListBox1) and two buttons:
Creating a UserForm to copy sheet to any open workbook

Next, double-click the form and paste the below code in the Code window:

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

With the UserForm in place, you can use one of the following macros to copy the active sheet to the workbook of your choosing.

Copy sheet to the beginning of the selected workbook:

Public Sub CopySheetToBeginningAnotherWorkbook() Load UserForm1 UserForm1.Show If (UserForm1.SelectedWorkbook <> "") Then activeSheet.Copy Before:=Workbooks(UserForm1.SelectedWorkbook).Sheets(1) End If Unload UserForm1 End Sub

Copy sheet to the end of the selected workbook:

Public Sub CopySheetToEndAnotherWorkbook() 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

When run in Excel, the macro will show you a list of all currently opened workbooks. You select the needed one and click OK:
A macro to copy the active sheet to the selected workbook

Excel macro to copy sheet and rename

When you copy a sheet in Excel, the replica is given a name in the default format like Sheet1 (2). The following macros can spare you the trouble of changing the default name manually.

This code duplicates the active worksheet, names the copy as "Test Sheet" (you are free to replace it with any other name you like), and places the copied sheet at the end of the current workbook.

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

To allow the user to specify the name for the copied sheet, use this code:

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

Upon running, the macro displays the following input box, in which you type the desired name and press OK:
Excel macro to copy sheet and rename

Excel macro to copy sheet and rename based on cell value

In some situations, it may be more convenient to name a copy with a specific cell value, for example, a column header. For this, you simply take the above code and supply the value of the currently selected cell to the input box automatically. As with the previous example, the copy will be placed at the end of the active workbook.
Excel macro to copy sheet and rename based on cell value

The trickiest part would be to have your users always select the correct cell before running the macro :)

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

Alternatively, you can hardcode the address of the cell by which the copy should be named, cell A1 in the below code. To name the copied worksheet based on another cell, replace A1 with an appropriate cell reference.

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

Macro to copy worksheet to a closed workbook

This macro copies the active sheet to the end of a closed workbook. The name of another workbook is not specified in the code - the macro will open the standard Windows Explorer window and allow you to choose any destination file:
Macro to copy worksheet to a closed workbook

After you select the file and click Open, the macro will copy the active sheet and close the target workbook automatically.

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 to copy sheet from another workbook without opening

This macro enables you to copy a worksheet from another Excel file without opening it. The copied sheet will be inserted at the end of the current workbook.

Just remember to make a couple of replacements in the code:

  • C:\Users\XXX\Documents\Target_Book.xlsx should be changed to the actual path and name of the workbook from which you want to copy a sheet.
  • Sheet1 should be replaced with the name of the sheet you want to copy.

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

Excel VBA to duplicate sheet multiple times

Sometimes, you may need to duplicate the same sheet more than once, for instance to test different formulas on the same data set. This can be easily done with the following macro.

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

Open the original sheet, run the macro, specify how many copies of the active sheet you want to make, and click OK:
Excel VBA to duplicate sheet multiple times

How to duplicate sheets in Excel with VBA

To copy a sheet in Excel with one of the above macros, you can either insert the VBA code into your own book or run a macro from our sample workbook.

How to add a macro to your workbook

To insert the code in your workbook, perform these steps:

  1. Open the worksheet you want to copy.
  2. Press Alt + F11 to open the Visual Basic Editor.
  3. On the left pane, right-click ThisWorkbook, and then click Insert > Module.
  4. Paste the code in the Code window.
  5. Press F5 to run the macro.

For the detailed step-by-step instructions, please see How to insert VBA code in Excel.

How to run a macro from our sample workbook

Alternatively, you can download our sample workbook to Duplicate Excel Sheets and run the code from there.

The sample workbook contains the following macros:

CopySheetToNewWorkbook - copies the current worksheet to a new workbook.

CopySelectedSheets - copies multiple sheets that you select to a new workbook.

CopySheetToBeginningAnotherWorkbook - copies the active sheet to the beginning of another workbook.

CopySheetToEndAnotherWorkbook - copies the active sheet to the end of another Excel file.

CopySheetAndRename - duplicates the current sheet, renames it as specified by the user, and puts the copy after all other sheets in the current workbook.

CopySheetAndRenamePredefined - duplicates the active sheet, gives a hardcoded name to the copy and places it at the end of the current workbook.

CopySheetAndRenameByCell - makes a copy of the active sheet and renames it based on the selected cell value.

CopySheetAndRenameByCell2 - copies the active sheet and renames it based on the hardcoded cell address.

CopySheetToClosedWorkbook - allows you to copy sheet to a closed workbook.

CopySheetFromClosedWorkbook - enables you to copy a sheet from another Excel file without opening it.

DuplicateSheetMultipleTimes - lets you duplicate a sheet in Excel multiple times.

To run the macro in your Excel, just do the following:

  1. Open the downloaded workbook and enable the content if prompted.
  2. Open your own workbook and navigate to the sheet you want to copy.
  3. In your worksheet, press Alt + F8, select the macro of interest, and click Run.

Copy a sheet in Excel using a macro.

That's how you can duplicate a sheet in Excel with VBA. I thank you for reading and hope to see you on our blog next week!

Table of contents

44 comments

  1. Hi, Quick question. Is there a VBA script if I want to copy Sheet 1 and Sheet 2 on another worksheet and then split it into based on column values ?

    ex. I have detailed data and I summarize it, I want to Copy thise 2 sheet into new multiple files based on my column category

  2. Hi i am trying to copy and re name based on your example above, The first tab is fine, but each tab after keeps copying the first tab. i need it to copy the most recent tab. what am i doing wrong?

    • Hi! The article above has many examples. I don't know which one you are using. Check if you have written the code correctly. Also note that the macro copies the active sheet.

  3. Hello,

    First, let me say how every time i look into some office option, especially Excel, i'm amazed how people like you share their knowledges so freely and sometimes in more precise and helpful ways than from Microsoft. It may look mundane to you and not to be a suck up but i think you guys are unknown teachers pushing humanity forward. It may look like your help is a simple formula to help me with my work (which is great!!) but i think it also contributes to general knowledge and may lead to the next person inventing a great algorythm.

    Ok, feet back on earth, I have been trying to make a special form on excel to be able to list our procedures, bids, etc... in a more uniform manner.
    I've created a template of that form and i would like to add a button so that user can create a new sheet (procedure) that automatically gets listed.
    My list is already done, gets updated every time that a new spreadsheet appears with the name of that sheet and also add an hyperlink to that sheet.

    For the button, i would need help with the code (i'm intermediary with excel so not so bad with excel formulas but beginner with VBA):
    I would like the button
    1 - to be on my listing sheet (List-PRO-FOR)
    2 - that it creates a copy of my template sheet (Blanc)
    3 - in the same workbook, following the last procedure that has been created (PRO-FOR-001)
    4 - that it gives it a new name following the last up to date on the list (PRO-FOR-002)

    Oh if you don't mind a second question...
    Would you know how i can hide hidden sheets from my listing?
    I used this formula : =IFERROR(HYPERLINK("#'"&INDEX(SheetName, ROW(A7))&"'!A1", INDEX(SheetName,ROW(A7))),"")
    where SheetName is =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&T(NOW())
    Now my list is updating but with every sheets created (hidden or not) and i would like only the list and numbered procedures to appear.

    THANK YOU SO MUCH !!

    • Hi! Unfortunately, your questions require you to create additional VBA code. This goes beyond the advice we provide on this blog.

  4. My task was simple: only to add a command button to a worksheet to be able to make a copy of the master sheet and be able to add a new name to it,,i waas able to use 1 of the listed codes you have and it works great
    Thank you all

  5. Hello ,
    I would like to seek help.
    I have some links in the sheets I create but when I duplicate the sheet or copy then that links stick to the original sheet and when pressing that link, it directs me to that original target.
    I would like to use that same links for the sheet specific

    Please advise on that

  6. I have to copy four or five worksheets out of 10 sheets to another workbook as values through vba. I need the names of the sheets in the coding to add or delete as the case may be.

  7. Fantastic post! Which I am hoping will allow me to remove instances of INDIRECT and it's issues.

    I need a combination of the "CopySelectedSheets - copies multiple sheets that you select to a new workbook." and "CopySheetToEndAnotherWorkbook - copies the active sheet to the end of another Excel file."

    i.e. select specific tabs and have them copied to an existing spreadsheet which is chosen from those that are open.
    I've tried using code from "CopySelectedSheets" to no avail.

    Thank you again!!!

  8. Hi, I have a Master Worksheet that has many other worksheets linked to it, the cells on the Master Sheet are referenced in the copied worksheets. How do I get this to work when I want to copy a new sheet and I want the new copied sheet to reference the master sheet cells +1.

    For Example. I have 30 projects I need to track, and I am tracking little bits of information as they come in, each row on the master sheet is a new project and Runs from Cells A3-AC3. I want everything entered in the cells A3-AC3 to show up automatically on the linked spreadsheet within the same Workbook. When I have to enter a New project, I want to have an Excel Macro create a copy of my Worksheet template, and automatically increment the referenced Cells, or alternatively prompt me for an input of at least what cell row to reference.

    Any help would be appreciated.

  9. Hi,

    I am running the "Excel macro to copy sheet and rename" where it allows the user to save a new name. However, when I create it, it renames the old version then creates a duplicate of the original and puts in for example test (2). I want to keep the original and have the new named sheet at the end of the workbook. Here is my coding:
    Sub CopySheetAndRename()
    Dim newName As String

    On Error Resume Next
    newName = InputBox("Enter the name for the copied worksheet")

    If newName "" Then
    Sheets("Portfolio Plus").Copy After:=Worksheets(Sheets.Count)
    On Error Resume Next
    Sheets("Portfolio Plus").Name = newName
    End If
    End Sub

    Thanks!

  10. I have a worksheet where I would like to copy two worksheets and rename them. The two worksheets are named by date. How could I amend the macro to name the two new worksheets with the date+1 of the previous worksheets?

  11. I have a template with named ranges in tables that I use to export data from SAS (One range per sheet, 9 sheets). I keep ending up with corrupted files and I was told to copy everything to a new workbook and try again. However, I get an error "You cannot copy or move a group of sheets that contain tables."
    I am very new to VBA, so any advice is appreciated!

  12. I have set up the COPY A WORKSHEET AND RENAME macro but it copies the new worksheet AFTER (to the right of) the source. I want it to copy before..I tried changing the "After" command to "Before" but this did not work. suggestions?

  13. Quiero copiar la hoja T3 y reemplazar con ella las hojas T1 y T2 sin que se disparen errores de Ref! en la hoja Indice

  14. HI
    Is there a way to run a macro to copy all comment thread/notes from cells to a new worksheet?
    I've tried and found VB code but it didnt seem to work, I'm using office 365.
    Thanks

  15. Hello.
    I am trying to place a shape button on sheets in a workbook so when data is filled for a week. I like copy this sheet say name of workbook is " working on" and sheet name " weekly report" when this page is filed for the week. I want copy this sheet in to closed work book say name is "weekly reports Archive" and place it at end of all sheets and name the sheet i am copying to "weekly report wk 21" after weekly report 20 sheet. All work books and sheets are shared for updates and want to make it more easier for others to update. Would you be able to help. I am new to VBA coding.

  16. I recived "path not found '\.vbbad70.tmp' while i used:
    Sheets(SheetCount).Copy After:=Sheets(SheetCount)

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)