Apr 12 2022 09:06 AM - edited Apr 12 2022 09:07 AM
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
Apr 12 2022 12:18 PM
Solutionws1, 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")
Apr 12 2022 12:39 PM
Apr 12 2022 12:18 PM
Solutionws1, 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")