Debug Run-time error '9;" Subscript out of range

[gheyman]

This code is erroring on row:

Sheets("TaskList").ListObjects(ModelPropricer_vdataNISTaskView).QueryTable.Refresh BackgroundQuery:=False

Dont know why. The sheet name is right and I tried changing the ListObject to the table name as well - no luck

Sub LoadProposal()
'*Move the Selected Proposal Data to the Selected Proposal Table
Application.ScreenUpdating = False


MsgBox "Before you use this tool you should have entered the PART NUMBER and MTRL CONSOLIDATION Fields in ProPricer/Task"

    Dim sh As Worksheet
    Dim tblPropList As ListObject
    Dim lastRow As ListRow
    Dim lr As Long
    
    Set sh = Sheets("Step 1")
    Set tblPropList = sh.ListObjects("ModelPropricer_vdataProposal")

    lr = Cells(Rows.Count, "B").End(xlUp).Row

    If Intersect(ActiveCell, Range("B16:M" & lr)) Is Nothing Then
        MsgBox "You must select a cell within the applicable Proposal Row.  Select a valid Cell"
    Exit Sub
    End If


    Sheets("Step 1").Range("B" & ActiveCell.Row & ":" & "M" & ActiveCell.Row).Copy
    Sheets("Selected Proposal").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
    Application.CutCopyMode = False
    
    Range("E3").Select
    ActiveCell.Formula2R1C1 = _
        "=""Loaded Proposal: "" & Selected_Proposal[Name] & "": "" & Selected_Proposal[Version]"
        
 'Validate that the user assigned Part Numbers to each Task in ProPricer

    Sheets("TaskList").Visible = True
    Sheets("TaskList").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    
 Sheets("Step 1").Select
        
        If Sheets("TaskList").Range("X1").Value < 1 Then
            MsgBox "You have not assigned any Part Numbers to your Proposal Task(s). If you do not do so, you will not be able to load your data and you will get error messages if you proceed."
        Else
        
        Sheets("Step 2A").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
        
        End If
        
 

 Application.ScreenUpdating = True
 
        
MsgBox "The selected proposal has been loaded. Go to Step 2"

End Sub

Thanks!

 

[RoryA]

That line isn't in your code. If it were, I suspect it should be:

Sheets("TaskList").ListObjects("ModelPropricer_vdataNISTaskView").QueryTable.Refresh BackgroundQuery:=False

 

[gheyman]

Isn't it in line 10 from the bottom?

 

[gheyman]

Sub LoadProposal()
'*Move the Selected Proposal Data to the Selected Proposal Table
Application.ScreenUpdating = False


MsgBox "Before you use this tool you should have entered the PART NUMBER and MTRL CONSOLIDATION Fields in ProPricer/Task"

    Dim sh As Worksheet
    Dim tblPropList As ListObject
    Dim lastRow As ListRow
    Dim lr As Long
   
    Set sh = Sheets("Step 1")
    Set tblPropList = sh.ListObjects("ModelPropricer_vdataProposal")

    lr = Cells(Rows.Count, "B").End(xlUp).Row

    If Intersect(ActiveCell, Range("B16:M" & lr)) Is Nothing Then
        MsgBox "You must select a cell within the applicable Proposal Row.  Select a valid Cell"
    Exit Sub
    End If


    Sheets("Step 1").Range("B" & ActiveCell.Row & ":" & "M" & ActiveCell.Row).Copy
    Sheets("Selected Proposal").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
       
    Application.CutCopyMode = False
   
    Range("E3").Select
    ActiveCell.Formula2R1C1 = _
        "=""Loaded Proposal: "" & Selected_Proposal[Name] & "": "" & Selected_Proposal[Version]"
       
       
' Validate that the user assigned Part Numbers to each Task in ProPricer

'XXXXXXX ERROR HERE XXXXXXXXXX Run-time error '9':  Sunscript out of range XXXXXXXX
Sheets("TaskList").ListObjects("ModelPropricer_vdataNISTaskView").QueryTable.Refresh BackgroundQuery:=False
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


 Sheets("Step 1").Select
       
        If Sheets("TaskList").Range("X1").Value < 1 Then
            MsgBox "You have not assigned any Part Numbers to your Proposal Task(s). If you do not do so, you will not be able to load your data and you will get error messages if you proceed."
        Else
       
        End If
       
 

 Application.ScreenUpdating = True
 
       
MsgBox "The selected proposal has been loaded. Go to Step 2"

End Sub

 

[RoryA]

No. That says Listobjects(1)

 

[gheyman]

Rory
I tried both Listobjects(1) and ListObjects("ModelPropricer_vdataNISTaskView") and get and get an error.

 

[RoryA]

Then the most likely cause is that the sheet name is wrong.

 

[gheyman]

TaskList
ModelPropricer_vdataNISTaskView


I will try Sheet28 and see if that helps

 

[gheyman]

Thanks. changing it to Sheet28 seems to have worked - but I am pretty sure that was the tab name. I copied and pasted it in several times.

But it works now and thats what matters.

Thank you Rory!