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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,554
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,084,859
Messages
5,380,324
Members
401,665
Latest member
iahmad

Some videos you may like

This Week's Hot Topics

Top