Dupicate sheet with new name which is as per master list.

Question

Hello -- There is one requirement that i have to duplicate this sheet 30 times criteria as shown below..

I have to duplicate this sheet S1....> 30 times.

The content of S1 sheet shall remain same for all 30 sheet

The name of the new sheet shall be renamed as S2.S3, S4,…....S30.

Please help me in this regard.

Answer 1

Hi @Chaturvedi, Santosh,

Thank you for posting your question in the Microsoft Q&A forum.

From your description, you would like to duplicate sheet S1 multiple times and rename each copy sequentially. This requirement is possible in Excel; however, it cannot be achieved in a single step using the standard user interface. You would need to duplicate the sheet manually or use a VBA (macro) approach to automate the process.

Please follow the steps below:

1. Open your Excel file.
2. Press Alt + F11 (or Fn + Alt + F11) to open the VBA Editor.
3. In the VBA Editor, go to Insert > Module.
4. In the module window, paste the following VBA code:

      Sub DuplicateAndRenameSheets() 
          Dim i As Integer 
          Dim baseSheet As Worksheet 
        
          Set baseSheet = ThisWorkbook.Sheets("S1") 
        
          For i = 2 To 30 
              baseSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 
              ActiveSheet.Name = "S" & i 
          Next i 
      End Sub 
   

   
5. Click Run (or press F5) to execute the macro.

Once completed:

• The original sheet S1 will remain unchanged.
• Additional copies will be created automatically.
• The new sheets will be named sequentially (S2, S3, S4, … S30).
• The content of all sheets will remain identical.

This approach helps you avoid repeating the duplication process manually and saves time.

Thank you for your patience and understanding. I appreciate your time, and I’m here to assist if you have any further questions or updates to share.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".                          

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread    

Answer 2

Use the worksheet copy feature and then rename each sheet.

1. Right-click the S1 sheet tab and select Move or Copy….
2. In the dialog, check Create a copy and choose where to place it, then select OK.
3. A copy of S1 is created with the same content. Rename it to S2:
   • Double-click the new sheet tab and type S2, or
   • Right-click the tab, select Rename, and type S2.
4. Repeat steps 1–3 to create additional copies and rename them S3, S4, …, S30.

To rename sheets efficiently, use the keyboard shortcut Alt+H, O, R on each selected sheet tab, then type the next name (S3, S4, etc.).

This keeps the content identical while giving each sheet its own name.

---

References:

• Programmatically copy worksheets
• Worksheet.Copy method (Excel)
• Rename a worksheet