SOLVED

VBA Help with Dim and Set tables using listobjects.

Occasional Contributor

Trying to set a table with listobjects, and it comes up with runtime error '438': Object doesn't support this property or method.  please help if you can, my code is below and it highlights the row "set tb1 = wb.ws1.listobjects("Totals")."  all the Set tb1, tb2, tb3, and tb4 come up in the locals box as value <nothing>.

 

Sub TWRefresh()


Dim wb As Workbook


Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet


Dim tb1 As ListObject
Dim tb2 As ListObject
Dim tb3 As ListObject
Dim tb4 As ListObject


Set wb = ThisWorkbook

 

Set ws1 = wb.Worksheets("Totals")
Set ws2 = wb.Worksheets("Gantt")
Set ws3 = wb.Worksheets("TWDataBO!")
Set ws4 = wb.Worksheets("EmpRoster!")

 

Set tb1 = wb.ws1.ListObjects("Totals")
    'Set tb2 = wb.ws2.ListObjects("")    No table in this worksheet yet...
Set tb3 = wb.ws3.ListObjects("TWDataBO")
Set tb4 = wb.ws4.ListObjects("Roster")

 

'Unprotect worksheets
wb.ws1.Unprotect Password:="***"
wb.ws2.Unprotect Password:="***"
wb.ws3.Unprotect Password:="***"
wb.ws4.Unprotect Password:="***"

'Run 1st level queries
wb.ws3.tb3.QueryTable.Refresh BackgroundQuery:=False
wb.ws4.tb4.QueryTable.Refresh BackgroundQuery:=False
wb.ws1.Activate
     'Application.OnTime Now + TimeValue("00:00:03"), "RefreshER2" 'Give time for 1st level queries to finish

End Sub

2 Replies
best response confirmed by ADGToreUp (Occasional Contributor)
Solution

@ADGToreUp 

ws1, ws2 etc. are already defined as worksheets of wb, so you should not use wb.ws1, wb.ws2 etc. Just ws1, ws2 etc. is sufficient:

 

Set tb1 = ws1.ListObjects("Totals")

Thank you! That was driving me crazy!