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.
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
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
Depending on where you want to insert the copied sheet, use one of the following macros.
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
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
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:
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:
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:
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.
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
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:
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
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:
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
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:
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.
To insert the code in your workbook, perform these steps:
For the detailed step-by-step instructions, please see How to insert VBA code in Excel.
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:
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!
14 Responses to "How to duplicate sheet in Excel with VBA"
That's great
how can I copy sheets containing a specific text string somewhere on the sheet, to a new workbook?
Thanks for the tip, is there a way to copy a row in one workbook and paste that row of data to a different workbook on a specific cell? There is a catch though, the data will need to sync to a different tab of the same workbook every other month.
How can i copy data from a range of cells say B5:I39 from multiple worksheets and paste them to one new worksheet, i want to be able to have all selected worksheets with specific data range to be copied to one worksheet at the end of the workbook. All formatting from selected sheets to be coppied across too.
Hello, your macros have been extremely helpful.
I was wondering if the following can be achieved
1) I select a range, say A2-A15, which contains the names of worksheets in a particular workbook.
2) I want to create a new workbook with the selected worksheet names from the active workbook using the following formula:
“
Sub Copy_Worksheets()
Worksheets(Array("ACC_HF", "ACC_Aflac", “etc…”)).Copy
End Sub
”
Is there a way to convert the selected range and pass is to the array argument?
thanks so much.
Is it possible to pick up a range of tabs? For example, the macro requires that I list the tab names individually ie.Sheets(Array ("Tab1", "Tab2", "Tab3", “etc…”)).Copy)
What if I want to pick up all the tabs between "Tab1" and "Tab8" without listing them all individually? Is this possible?
Hi,
I have a workbook with many sheets. I need particular cells copied from sheet 1,sheet2 of Workbook1 and show it in another workbook. For example I have the dealer ID and name in Sheet1 and the rating in sheet5 of the same workbook1. I need to pull the rating using ID which is unique to another worksheet and then I need the ID field to be dynamic. How do I do it? please advise.
Hi I have used your code titled "Excel VBA to duplicate sheet multiple times"
but how shall I rename the sheets? I want them to be in numbers.
Eg
Original Sheet is "master'
Copy need 5
result shall be in sheet name as
Point 1
Point 2
Point 3
Point 4
Point 5
Please help
---[Snip, Snip]---
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)
ActiveSheet.name = "Point " + CStr(numtimes)
Next
End If
End Sub
---[Snip, Sni
This will cause the various copied sheets to be renamed as they are copied
Thanks for this vba "Excel VBA to copy sheet from another workbook without opening" which works excellently in its current form. Can you please tweak it a little so that instead of giving a specific path, a window opens and I select the file from which data needs to be pulled. Rest it should remain the same. Thanks in advance.
Excel VBA to duplicate sheet multiple times:
This is the closest to what I am trying to do. But all the new sheets are named he same as the original but with (x). i.e. Original sheet is 10001 and I need them to number 10002, 10003, etc. But they are numbering 10001, 10001(1), 10001(2).
How can I get my sheet tabs to number the way I want? Any help would be greatly appreciated.
Thanks!
Excel macro to copy sheet and rename
Is there anyway for the button to be on one sheet but instead of copying the active sheet it copies the sheet called New Hire?
Hi,
Your macros have been extremely helpful.
I really appreciate your work. Thank you.
Thank you so much.
Hi
Is it possible to overwrite a sheet of the same name in the worksheet where the sheet is to be copied? I need to be able to copy the same sheet from time to time. Please help?
Hello,
I am trying to duplicate a excel sheet multiple times, rename them based on an Excel List, and then populate the column next to the sheet name with a link to the sheet.
The list is found in the "Cover" sheet and I want to copy the "Template" sheet based on the list selection. The link shortcut will be in the cover sheet in the column next to the cell that was used to generate the sheet name. There will be a lot of tabs and this will make life a lot easier if there is a link from the cover page
Is this possible, and if so how can I do it?