Creating x number of new sheets and populating with data from control sheet

[ChaosPup]

What I'm trying to do here is have a control sheet with a macro that looks at a serial number in cell B24, a unit quantity in cell B23 and a unit type in B8. When the macro runs I want it to create B23 numbers of sheets named "B8_B24" (i.e., unit type_serial number), and populate a few other cells with data that the user will define in the control sheet (i.e., tech initials in cell A5 on control sheet to cell A3 in all created sheets). Now, someone else in my office claims to have done something similar before, and gave the following VBA, which I can follow but there seems to be something fundamentally wrong that I can't put my finger on because I get a 'type mismatch error'.

Sub COPYFAT()

Application.DisplayAlerts = False   'Turns OFF error reporting overwrites file without prompting
Application.ScreenUpdating = False
  
   'Varible declarations
  
    Dim UnitNum As Variant      'Number of units
    Dim SheetName As Variant    'Name of sheet
    Dim I As Integer            'Counter
    Dim Start As Integer        'First SN
    Dim Increment As Integer    'SN+1
    Dim xNumber As Integer
    Dim xName As String
    Dim xActiveSheet As Worksheet
         
    Worksheets(Range("B8").Value).Activate  'Name of Sheet to copy from CELL B8 on control sheet
    Start = Range("B24")
    Set xActiveSheet = ActiveSheet
   
    UnitNum = Range("B23").Value - 1        'Number of units pulled from CELL B23 on control sheet ***-1 because index starts at ZERO***
   
    For I = 0 To UnitNum                    'Planning to add this number to cell reference for name starts at zero so initial reference is correct
        Increment = Start + I
        xName = ActiveSheet.Name
        xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)
        ActiveSheet.Name = "ID5001P-" & Increment
        ActiveSheet.Range("I4") = "ID5001P-" & Increment
    Next
    xActiveSheet.Activate
 
' ***************************************************************
' Turns ON error reporting
' **************************************************************

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Any assistance would be much appreciated!

 

[Joe4]

Note that you can specify exactly which sheet you want to pull the value from by adding a sheet reference to the range reference.
Then that line of code can appear anywhere you like, i.e.

    Start = Sheets("Control Sheet").Range("B24")

(just change "Control Sheet" to whatever the name of your control sheet is).

The way that VBA works, if if you leave the sheet reference off of the range reference, it defaults to whatever the active sheet is when it hits that line of code.

 

[ChaosPup]

Note that you can specify exactly which sheet you want to pull the value from by adding a sheet reference to the range reference.
Then that line of code can appear anywhere you like, i.e.

    Start = Sheets("Control Sheet").Range("B24")

(just change "Control Sheet" to whatever the name of your control sheet is).

The way that VBA works, if if you leave the sheet reference off of the range reference, it defaults to whatever the active sheet is when it hits that line of code.

Perfect, thanks so much for your help.

 

[Joe4]

You are welcome.