How Do I Duplicate a Sheet Multiple Times in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Download Practice Workbook

How to Duplicate a Sheet in Excel

2 Suitable Examples to Duplicate a Sheet Multiple Times in Excel

1. Duplicate a Sheet Multiple Times

2. Duplicate a Sheet Multiple Times and Rename

Conclusion

Download Practice Workbook

Download the practice workbook below.

Duplicate a Sheet Multiple Times.xlsm

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.

2 Suitable Examples to Duplicate a Sheet Multiple Times in Excel

To duplicate a sheet multiple times in Excel, we have found two different examples through which you can have a clear idea of 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.

🔎 VBA Code Explanation:

Sub Duplicate_Sheet()

First of all, provide a name for the sub-procedure of the macro.

Dim i As Integer

Next, declare the necessary variable for the macro.

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

Next, it will show an input box to take the number of sheets the user wants to make.

For numtimes = 1 To i

After taking the input, we will run the loop.

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

Then, it will copy the annual loan payment worksheet.

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

After that, it will create a duplicate excel sheet of the given worksheet.

Next
End Sub

Finally, end the sub-procedure of macro

Similar Readings

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 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.

🔎 VBA Code Explanation:

Sub copy_multiple_times_rename()

First of all, provide a name for the sub-procedure of the macro.

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

Next, declare the necessary variable for the macro.

On Error Resume Next
Application.ScreenUpdating = False

It will help us to run the code in the background.

Set current_sheet = ActiveSheet

Then, it will activate the sheet that you want to copy.

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

Next, it will show an input box to take the number of sheets the user wants to make.

For i = 1 To num_of_sheets

After taking the input, we will run the loop.

sheet_name = ActiveSheet.Name

Then, it will store the sheet name of “Annual Loan Payment”.

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

It will copy the “Annual Loan Payment” sheet and paste it after the “Annual Loan Payment” worksheet.

ActiveSheet.Name = "Copy-" & i

By this, we rename the sheet. Basically, we will rename the worksheet starting with “Copy-”. You will see this later.

Next
current_sheet.Activate

Next, it will activate the “Annual Loan Payment” sheet.

Application.ScreenUpdating = True
End Sub

Finally, end the sub-procedure of the macro

Conclusion

Instead of doing the work manually, we may use some 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. Don’t forget to visit our Exceldemy page.