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.
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.
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.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
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:
You are guest
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 years agoIs it possible that you add in this code an option to also copy specific cells. For example: I have in one cell Number 1 and I would like that when I duplicate Sheet will change for +1. Thanks
To post as a guest, your comment is unpublished.· 4 years agoWow this saved a lot of time for me! Thank you!