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]

When you hit the "Debug" button when you get the error, which line of code does it highlight?

 

[ChaosPup]

When you hit the "Debug" button when you get the error, which line of code does it highlight?

It highlights -

Start = Range("B24")

 

[Joe4]

What is the exact value you have in cell A24?

If you enter this formula in any blank cell on your sheet, what does it return?
=ISNUMBER(A24)

 

[ChaosPup]

What is the exact value you have in cell A24?

If you enter this formula in any blank cell on your sheet, what does it return?
=ISNUMBER(A24)

For A24 I get FALSE. For B24 I get TRUE.

I have the Control sheet that I'm running the macro on as sheet1 and the template I want to use as sheet2, does that make a difference? The name of the template sheet is what is in B8

 

[Joe4]

I am sorry, I meant was in in cell B24, and what does that formula return for cell B24.
Actually, first we need to know what is in cell B8, and what is in cell B24 of the sheet mentioned in cell B8.

 

[ChaosPup]

I am sorry, I meant was in in cell B24, and what does that formula return for cell B24.
Actually, first we need to know what is in cell B8, and what is in cell B24 of the sheet mentioned in cell B8.

OK so in B8 of the Control sheet there is the text ID5001P FAT which is the name of the template sheet. I think I'm doing something basic wrong here - the number in B24 (which could be anything) should refer to the B24 also in the control sheet, but if I'm activating the template sheet first is it looking for the contents of B24 on the template sheet?

 

[Joe4]

but if I'm activating the template sheet first is it looking for the contents of B24 on the template sheet?

Yes. Since you are activating the "ID5001P FAT" sheet before that step, it should be looking at cell B24 on that "ID5001P FAT" sheet.

So back to my original question, what exactly is in cell B24 on that "ID5001P FAT" sheet?
And are we sure it is entered as a number and not text (i.e. using the ISNUMBER formula on it)?

 

[ChaosPup]

Yes. Since you are activating the "ID5001P FAT" sheet before that step, it should be looking at cell B24 on that "ID5001P FAT" sheet.

So back to my original question, what exactly is in cell B24 on that "ID5001P FAT" sheet?
And are we sure it is entered as a number and not text (i.e. using the ISNUMBER formula on it)?

Ah, I see. B24 on the ID5001P FAT sheet is just text, not a number. It needs to refer to B24 on the Control sheet where the macro button is. I've tried moving that line above the Worksheets(Range("B8").Value).Activate line but all it does is swap to the ID5001P FAT sheet and nothing else.

 

[ChaosPup]

Ah, I see. B24 on the ID5001P FAT sheet is just text, not a number. It needs to refer to B24 on the Control sheet where the macro button is. I've tried moving that line above the Worksheets(Range("B8").Value).Activate line but all it does is swap to the ID5001P FAT sheet and nothing else.

Ah, I got it. I moved the UnitNum = Range("B23").Value - 1 line to the top and it auto generated the way I wanted it to