Sequentially Number copies of template worksheet

[jwb1012]

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.

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

 

[Peter_SSs]

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).

                    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.