• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • 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.
0
dbfromnewjersey
Asked:
dbfromnewjersey
2 Solutions
 
Rgonzo1971Commented:
Hi,

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
Next
Sheets(1).Delete

End Sub

Open in new window

Regards
0
 
nutschCommented:
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

0
 
vmaganCommented:
You can also right click on the sheet name and click on move and copy. You will be able to copy it there.
0
 
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

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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