How Do I Duplicate a Sheet Multiple Times in Excel

Sometimes, you need to duplicate a sheet in Excel multiple times. To do it manually one after another is a very time-consuming process. Microsoft Excel provides you with such a platform that you can easily do some work with ease. In this article, we will focus on how to duplicate a sheet in Excel multiple times effectively.

How to Duplicate a Sheet in Excel

When you work in Microsoft Excel, a situation may arise where you just need to change a little in the comment sheet. At that time, it is better to create a duplicate of this current sheet and change the item. To understand the process clearly, follow the steps.

Steps

• First, right-click on the worksheet.
• Then, select Move or Copy.

• After that, the Move or Copy dialog box will appear.
• Then, select the move to end option.
• Next, check on Create a copy.
• Finally, click on OK.

• As a result, we will get the desired duplicate of the worksheet. See the screenshot.

How Do I Duplicate a Sheet in Excel Multiple Times: 2 Suitable Examples

To duplicate a sheet multiple times in Excel, we have found two different examples through which you can clearly understand this topic. In this article, we would like to utilize the VBA code. Using these VBA codes, we can easily duplicate a sheet numerous times in Excel. Here, we would like to figure out how to use the VBA codes and their possible explanations. Follow the examples clearly.

1. Duplicate a Sheet Multiple Times

Our first and most important method to duplicate a sheet in Excel multiple times is to use VBA codes. By applying the VBA code, we can easily create duplicate sheets numerous times. In this method, we would like to discuss the possible VBA code for this problem and its explanation. We take a dataset including the annual loan payment calculator. We would like to change the interest rate, payment amount, and period in other sheets. So, we would like to create multiple duplicate sheets rather than create each one individually.

To understand the process clearly, follow the steps.

Steps

• First, go to the Developer tab on the ribbon.
• Then, select the Visual Basic option from the Code group.

• It will open up the Visual Basic window.
• Then, go to the Insert tab at the top.
• After that, select the Module option.

• As a result, a Module code window will appear.
• Write down the following code.

Sub Duplicate_Sheet()
Dim i As Integer
i = InputBox("Enter number of times to copy Annual Loan Payment")
For numtimes = 1 To i
ActiveWorkbook.Sheets("Annual Loan Payment").Copy _
After:=ActiveWorkbook.Sheets("Annual Loan Payment")
Next
End Sub

• Then, close the Visual Basic window.
• Then, go to the Developer tab on the ribbon.
• Select the Macros option from the Code group.

• Then, the Macro dialog box will appear.
• Select Duplicate_Sheet from the Macro name section.
• After that, click on Run.

• As a result, a message box will appear.
• Then, enter how many times you want to copy the annual loan payment.
• After that. Click on OK.

• Finally, you will get your desired results with multiple duplicate sheets. See the screenshot.

Sub Duplicate_Sheet()

Dim i As Integer

i = InputBox("Enter number of times to copy Annual Loan Payment")

For numtimes = 1 To i

ActiveWorkbook.Sheets("Annual Loan Payment").Copy _

After:=ActiveWorkbook.Sheets("Annual Loan Payment")

Next
End Sub

Similar Readings

• How to Copy Multiple Sheets to New Workbook in Excel
• How to Copy Excel Sheet to Another Sheet with Same Format
• Excel Copy Worksheet to Another Workbook Without Reference
• How to Copy a Sheet to Another Sheet in Excel
• Excel VBA to Copy Multiple Sheets to New Workbook

2. Duplicate a Sheet Multiple Times and Rename

In this section, I will show you step-by-step procedures to copy a sheet multiple times and rename it in Excel. Sometimes, you need a template of a worksheet that you can use several times in the workbook. This method will help you to copy that template in Excel using VBA. We take a dataset including the annual loan payment calculator. We would like to change the interest rate, payment amount, and period in other sheets. So, we would like to create multiple duplicate sheets rather than create each one individually.

To understand the VBA code properly, follow the steps.

Steps

• First, go to the Developer tab on the ribbon.
• Then, select the Visual Basic option from the Code group.

• It will open up the Visual Basic window.
• Then, go to the Insert tab at the top.
• After that, select the Module option.

• As a result, a Module code window will appear.
• Write down the following code.

Sub copy_multiple_times_rename()
Dim i As Long
Dim num_of_sheets As Integer
Dim sheet_name As String
Dim current_sheet As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Set current_sheet = ActiveSheet
num_of_sheets = InputBox("Enter number of times to copy the current sheet")
For i = 1 To num_of_sheets
sheet_name = ActiveSheet.Name
current_sheet.Copy After:=ActiveWorkbook.Sheets(sheet_name)
ActiveSheet.Name = "Copy-" & I
Next
current_sheet.Activate
Application.ScreenUpdating = True
End Sub

• Then, close the Visual Basic window.
• Then, go to the Developer tab on the ribbon.
• Select the Macros option from the Code group.

• Then, the Macro dialog box will appear.
• Select copy_multiple_times_rename from the Macro name section.
• After that, click on Run.

• As a result, a message box will appear.
• Then, enter how often you want to copy the annual loan payment.
• After that. Click on OK.

• Finally, you will get your desired results with multiple duplicate sheets. See the screenshot.

Sub copy_multiple_times_rename()

Dim i As Long
Dim num_of_sheets As Integer
Dim sheet_name As String
Dim current_sheet As Worksheet

On Error Resume Next
Application.ScreenUpdating = False

Set current_sheet = ActiveSheet

num_of_sheets = InputBox("Enter number of times to copy the current sheet")

For i = 1 To num_of_sheets

sheet_name = ActiveSheet.Name

current_sheet.Copy After:=ActiveWorkbook.Sheets(sheet_name)

ActiveSheet.Name = "Copy-" & i

Next
current_sheet.Activate

Application.ScreenUpdating = True
End Sub

Read More: Excel VBA to Copy and Rename a Worksheet Multiple Times

Download Practice Workbook

Download the practice workbook below.

Conclusion

Instead of doing the work manually, we may use other processes to do it quickly and effectively. In this article, we have shown some efficient procedures through which we can easily duplicate a sheet in Excel multiple times. Here, we have also included the necessary VBA code which is perfectly applicable to duplicating a certain sheet multiple times in Excel. We try our best to cover all possible areas of this topic. If you have any more queries, feel free to ask in the comment box.

Related Articles

• How to Copy Worksheet to Another Workbook Using VBA
• How to Copy Sheet to End in Excel Using VBA
• Excel VBA: Copy Worksheet to Another Workbook Without Opening
• VBA to Copy Excel Worksheet to Another Workbook without Formulas
• How to Copy Sheet to Another Workbook with Excel Formulas
• How to Copy Sheet with VBA in Excel
• How to Copy Excel Sheet into Word
• [Fixed!] Move or Copy Sheet Not Working in Excel
• VBA to Copy Worksheet to Another Workbook and Rename