Subscript out of range error

[yerromnitsuj]

I am trying to extract data from many similar workbooks. I have some code but I am getting a "subscript out of range" error. Here is the code (sorry about its length):

Sub Run_All_States()

Dim i As Integer
Dim j As Integer

Dim Indication_File As String
Dim Trunc_Indication_File As String
Dim Dropdown_Selections As String
Dim State As String

Dim New_Eff_date As String
Dim CAT_Load As Double

'Loop to go through each state and company listed

For i = 1 To Worksheets(SheetA).Range("B3").Value 'Number of states
    
    If Worksheets(SheetB).Cells(i + 1, 6) = Worksheets(SheetA).Range("B5") Then
    
    'Application.Calculation = xlCalculationManual

    'Make the template workbook
    Template_File = Worksheets(SheetA).Range("B2") & "Q" & Worksheets(SheetA).Range("B1") & " Template (" & _
        Worksheets(SheetA).Range("B5") & ")"
    
    'NB Effective Date for the indications
        'Does CA need special treatment here?
    New_Eff_date = Worksheets(SheetA).Range("B4")
    
    
    'Get the state
    State = Worksheets(SheetB).Cells(i + 1, 1)
    
    'CAT Load
    CAT_Load = Worksheets(SheetB).Cells(i + 1, 11)
    
    'Last NB Effective Date for the indications
    Last_NB_Eff_date = Worksheets(SheetB).Cells(i + 1, 12)
    
    'Last RB Effective Date for the indications
    Last_RB_Eff_date = Worksheets(SheetB).Cells(i + 1, 13)

    'Get the file name
    Indication_File = Worksheets(SheetB).Cells(i + 1, 9)
    Trunc_Indication_File = Worksheets(SheetB).Cells(i + 1, 10)
    
    'Select the correct combo list dependent upon state size
    Dropdown_Selections = Worksheets(SheetB).Cells(i + 1, 7)
    
    'Open the state's indication file
    Workbooks.Open Filename:=Indication_File, UpdateLinks:=0
    
    Windows(Trunc_Indication_File).Activate
        
    'Universal New Business Date
        'Does CA need special treatment here?
    Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range("B10") = New_Eff_date
        
    'State Specific CAT Load
    Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range("B18") = CAT_Load
        
    'State Specific Last NB Date
    Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range("B15") = Last_NB_Eff_date
    
    'State Specific Last RB Date
    Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range("B16") = Last_RB_Eff_date
    
    'Loop through the combinations
    For j = 1 To Workbooks(Template_File).Worksheets(SheetB).Cells(i + 1, 8).Value

        Windows(Trunc_Indication_File).Activate
        
        'Historical Loss Trend
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Loss_Type_Hist & Loss_Type_HistRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 2)
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Loss_Fit_Hist & Loss_Fit_HistRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 3)
        
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Loss_Type_Proj & Loss_Type_ProjRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 4)
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Loss_Fit_Proj & Loss_Fit_ProjRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 5)
        
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Prem_Type_Hist & Prem_Type_HistRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 8)
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Prem_Fit_Hist & Prem_Fit_HistRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 7)
        
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Prem_Type_Proj & Prem_Type_ProjRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 11)
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Prem_Fit_Proj & Prem_Fit_ProjRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 10)
        
        'Loss Type
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Loss_Type & Loss_TypeRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 12)
        
        'Loss Capping
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Loss_Capping & Loss_CappingRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 13)
            
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(LDFSelection & LDFSelectionRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 14)
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(LDFSelectionReserving & LDFSelectionReservingRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 15)
        
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Proj_LR & Proj_LRRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 16)
        

        Windows(Trunc_Indication_File).Activate
        Application.Run "'" & Indication_File & "'!" & "Update_LDF_ActiveFile"
        

        Windows(Trunc_Indication_File).Activate
        Sheets(Summary).Select
        Range("J4:J19").Select
        Selection.Copy
        Windows(Template_File).Activate
        Sheets(Template_Sheet).Select
        Cells(j + 3, 3).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Application.CutCopyMode = False
        
        'Put the state
        Cells(j + 3, 2) = State
        
        'Put the index number
        Cells(j + 3, 1) = j
        
    Next j
    
    'Close the indication without saving it
    Windows(Trunc_Indication_File).Activate
    ActiveWorkbook.Close savechanges:=False

    'Create a copy of the template, onto its state sheet

    Windows(Template_File).Activate
    Sheets(Template_Sheet).Select
    Cells.Select
    Selection.Copy
    Sheets(State).Select

    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.Zoom = 55

    ActiveSheet.Range("A1").Select

    'Clear the template
    Sheets(Template_Sheet).Select
    Range(Cells(4, 1), Cells(Worksheets(Template_Sheet).Range("A4").End(xlDown).Row, 18)).Select
    Selection.ClearContents

    End If

I am getting the error here:

For j = 1 To Workbooks(Template_File).Worksheets(SheetB).Cells(i + 1, 8).Value

Hopefully I've given enough info. If you need any details feel free to ask. Any ideas of what might be causing the error?

 

[yerromnitsuj]

I figured it out. I changed the the name of the template file the code is referencing.