Subscript out of range error?

[olimits7]

Hi,

I'm getting the "subscript out of range" error and I'm not sure what's causing it.

When I debug it, it stops at the following point:

 ActiveSheet.ListObjects("Table__2020_RevTM.accdb").Range.AutoFilter Field:=1

This is the whole function, it basically gives me this error when I save the new exported document to my desktop. When I look at the "Table__2020_RevTM.accdb" qryRev table in Access it seems to show up fine and the data also shows up fine in the file that I'm running this macro from.

Sub Export_Rep()
    Application.ScreenUpdating = False
    
    selRep = Range("selRep")  ' set var of rep name
    Qtr = Range("Qtr")        ' set qtr
    ConsQtr = Qtr & " Summary"   ' set var of consolidated quarter
    
    Call nwWorkbook(selRep, Qtr, ConsQtr)
    Call Comm_Export(selRep, Qtr, ConsQtr)
    Call Rev_Export(selRep, Qtr, ConsQtr)
    Call TM_Export(selRep, Qtr, ConsQtr)
    Call Export_SaveAs(selRep, Qtr, ConsQtr)
    
    Sheets("Reps REV").Select
    ActiveSheet.ListObjects("Table__2020_RevTM.accdb").Range.AutoFilter Field:=1
    
    Sheets("Reps T&M").Select
    ActiveSheet.ListObjects("Table__2020_RevTM.accdb28").Range.AutoFilter Field:=1
    
    Sheets(1).Select

End Sub

Does anyone have an idea of what could be causing this issue?

Thank you!

 

[olimits7]

Does anyone have an idea or can help point me in the right direction to see what is causing this?

Thank you!

 

[shg]

Enter ...

? Activesheet.ListObjects(1).Name

... in the Immediate window. What's it return?

 

[olimits7]

Hi @MrExcel MVP

You mean add the following to my code and then try running this again?

Sheets("Reps REV").Select
    ActiveSheet.ListObjects("Table__2020_RevTM.accdb").Range.AutoFilter Field:=1
    Print ActiveSheet.ListObjects(1).Name
    
    Sheets("Reps T&M").Select
    ActiveSheet.ListObjects("Table__2020_RevTM.accdb28").Range.AutoFilter Field:=1

When I do this I get a compile error: method not suitable without proper object

Thank you!

 

[shg]

No, that's not what I mean.

If you enter what I posted in the Immediate window, it will return the name of the table. What you have is incorrect; that's why you're getting the subscript error.

 

[olimits7]

Oh ok, I see what you mean; when I did that I see it shows "Table__2019_RevTM.accdb" but I'm not sure why it's still pointing to that table when I changed the code to show "Table__2020_RevTM.accdb".

In the Queries & Connections window for this connection I see under the "Used In" tab it's showing "Table__2019_RevTM.accdb" but not sure where it's pulling this name from.

Thank you, again!

 

[olimits7]

Oh ok, I was able to see under the "Name Manager" window the name was still showing 2019 instead of 2020; when I made this change it works properly now.

Thank you for pointing me in the right direction!

 

[shg]

Glad you got it sorted, you're welcome.