How to copy sheet and rename automatically in Excel?

In Excel, how could you copy a worksheet and rename it automatically without typing the name as usual? In this article, I will talk about how to copy a sheet one time or multiple times and give them specific names at once.

Copy a worksheet multiple times and rename them with increment tab names by using VBA code

Copy a worksheet multiple times and rename them with custom names by using Kutools for Excel

Copy a worksheet multiple times and rename them with increment tab names by using VBA code

If you need to copy a worksheet multiple times, and give them series names, such as KTE-1, KTE-2, KTE-3, KTE-4…, the following VBA code can do you a favor.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Copy a worksheet multiple times and rename at once:

Sub Create()
'Updateby Extendoffice
    Dim I As Long
    Dim xNumber As Integer
    Dim xName As String
    Dim xActiveSheet As Worksheet
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xActiveSheet = ActiveSheet
    xNumber = InputBox("Enter number of times to copy the current sheet")
    For I = 1 To xNumber
        xName = ActiveSheet.Name
        xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)
        ActiveSheet.Name = "KTE-" & I
    Next
    xActiveSheet.Activate
    Application.ScreenUpdating = True
End Sub

3. Then press F5 key to run this code, and a prompt box will remind you how many copies of the current worksheet that you want to copy, see screenshot:

4. Enter the number of copies into the popped dialog, and then click OK button, your current worksheet has been duplicated 5 copies and renamed with sequence name as following screenshot shown:

Note: In the above code, you can change the worksheet name text “KTE-” to your needed.

Copy a worksheet multiple times and rename them with custom names by using Kutools for Excel

Maybe the above VBA codes are difficult for you to apply, here, I will introduce an easy tool- Kutools for Excel, with its Create Sequence Worksheets utility, you can achieve this task without any effort.

After installing Kutools for Excel, please do as follows:

1. Click Kutools Plus > Worksheet > Create Sequence Worksheets, see screenshot:

2. In the Create Sequence Worksheets dialog box:

(1.) Select the worksheet name that you want to copy from the Base Worksheet drop down list;

(2.) Then specify the number of worksheet that you want to copy under The number of create text box;

(3.) And then check Sequence Numbers under the Sheet names based on section;

(4.) At last, create the sequence worksheet names in the right pane that you want, see screenshot:

3. After finishing the settings, please click Ok button, and the specific worksheet has been copied multiple times and renamed based on your created sequence in a new workbook, see screenshot:

Click Free Download Kutools for Excel Now !

Demo: Copy a worksheet multiple times and rename them with custom names by using Kutools for Excel