Sequentially Number copies of template worksheet

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Hello, I am using the following code to create multiple copies of a template, based on the "number of copies required" which is determined by a formula in cell D10 on the "Staffing Plan" worksheet.

If D10 = 5, then it will make 5 copies. It then renames the worksheets to give me: Report 1 of 5, Report 2 of 5, Report 3 of 5, Report 4 of 5, and Report 5 of 5.

I am wondering if its possible to also sequentially number the worksheets in cell A1. So, in cell A1 of each of these worksheets would have a number (1, 2, 3, 4, and 5). I need this number because the template (and the copies generated) is set up to do vlookups off of cell A1.


Code:
Sub Copy_Template()
    Dim Sh As Worksheet
    Dim i As Integer
    Dim i2 As Integer
    Dim X As Integer
        
        'Set sheet to copy
        Set Sh = Worksheets("Template")
        
            'Determine number of copies to make
            i = Worksheets("Staffing Plan").Range("D10")
        
                'Name the first sheet
                Sh.Name = "Template"
        
                    i2 = ActiveWorkbook.Worksheets.Count
                        For X = 1 To i
                            Sh.Copy After:=Sheets(i2 + X - 1)
                            'Name of subsequent sheets
                            Sheets(X + i2).Name = "Report " & X & " of " & i
                            
                        Next X
End Sub
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,401
Office Version
365
Platform
Windows
I am wondering if its possible to also sequentially number the worksheets in cell A1. So, in cell A1 of each of these worksheets would have a number (1, 2, 3, 4, and 5).

Code:
                    i2 = ActiveWorkbook.Worksheets.Count
                        For X = 1 To i
                            Sh.Copy After:=Sheets(i2 + X - 1)
                            'Name of subsequent sheets
                            Sheets(X + i2).Name = "Report " & X & " of " & i
                            [COLOR="#0000CD"][B]Sheets(X + i2).Range("A1").Value = X[/B][/COLOR]
                        Next X
End Sub
Untested, but it seems to me that adding the blue line above should do that for you.
 

Forum statistics

Threads
1,089,194
Messages
5,406,735
Members
403,105
Latest member
gituncar

This Week's Hot Topics

Top