refresh query table on hidden sheet

[gvanhook]

I am starting to learn about workbook queries in Excel 2016. I have a workbook query that I maintain but would like to keep hidden in my workbook and only show the related pivot table reports. Is it possible to refresh the data in the workbook query with the data sheet hidden? I have tried the following, and it works if I leave the sheet visible, but if I hide the sheet I get a run-time 1004 error Select method of worksheet class failed.

Code: Select all

Sub Refresh_Payroll2()
'
' refresh_Payroll Macro
'

'
    Sheets("Payroll Research Data").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Payroll Research").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
End Sub

I have also tried this code but get a run-time error 438: Object doesn't support this property or method.

Code: Select all

Sub Refresh_Payroll2()
'
' refresh_Payroll Macro
'

'
    Sheets("Payroll Research Data").ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Payroll Research").PivotTables("PivotTable2").PivotCache.Refresh
End Sub

Is there a way to refresh the data without making the sheet visible, or would I need to un-hide the sheet, perform the update and then hide it again? If I need to do the latter, how would I code that?

Thanks,
Greg

[Rudi]

Try this:

Your listobject has a name; select a cell in the table and click on the "Table Tools" ribbon. Take note of the name of the table in the far left of the ribbon (and/or give it a name of your choice). In the code line below I refer to the table called "Test".

Then edit your code to include the name of the listobject. ("Test" in my example).
Notice also the plural reference to ListObject!!

The fact that you now have a reference to the listobject allows it to successfully refresh even if the sheet is hidden.

Code: Select all

    Sheets("Payroll Research Data").ListObjects("Test").QueryTable.Refresh BackgroundQuery:=False

[gvanhook]

Thank you. That worked great!