• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

Make multiple copies of a worksheet in an Excel file

I have an Excel file with one worksheet named "Sheet1". Sheet1 contains a "shell".  I need the file to contain 20 copies of Sheet1 and the tabs to be named by the numbers 1 through 20.  In other words, rename Sheet1 to "1" and make 19 additional copies of it numbered 2 through 20. The expected result is an Excel file with 20 tabs numbered 1 through 20; each containing a copy of the "shell" from Sheet1.
2 Solutions

pls try

Sub macro()
Dim sh As Sheets
For Idx = 2 To 21
    Sheets(1).Copy after:=Sheets(Idx - 1)
    Sheets(Idx).Name = Idx - 1

End Sub

Open in new window

This code should do exactly that:
Sub Macro1()
Dim lLoop As Long

For lLoop = 2 To 20
    Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = lLoop
Next lLoop

Sheets("Sheet1").Name = "1"

End Sub

Open in new window

You can also right click on the sheet name and click on move and copy. You will be able to copy it there.
Santosh GuptaCommented:
Try this...

if you want to put some specific name you can user below Macro.

"LIST" - name of sheets
"Template" -your actual sheet.
Sub CreateSheets()
    Dim i As Long
    Dim ws As Worksheet
    Dim ws1 As Worksheet
        Set ws = Sheets("LIST")
        Set ws1 = Sheets("Template")
        For i = 2 To ws.Cells(Rows.Count, "A").End(xlUp).Row
            ws1.Copy After:=Sheets(Sheets.Count)
            With ActiveSheet
                .Name = ws.Cells(i, "A").Value
               End With
        Next i
End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now