Macro - Run Time 91 Error

[LottieRoseC]

Macro - Run Time 91 Error
Hi,

I have an excel document that is linked to access, and a macro runs to refresh all of the links and the pivots in excel that run off the data. My spreadsheet was working fine, and refreshing, but now when I run the Macro to refresh everything I get a Run Time error 91 Object Variable or With Block Variable not set. If I go to debug it highlights the following part of the code

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

I have checked that all the tables will refresh manually (which they do) and the connections all work. Does anyone know what the problem is?

Thanks

 

[Jerry Sullivan]

Hi and Welcome to the Board,

What is the code that comes before the line of code that you posted?

If "Selection" hasn't been set to an Object that has as a ListObject, then you could get that error. This might have happened if you recorded a macro by clicking on the Table at Cell A1, then later moved the Table to Cell A2.

Typically it's better to avoid the use of Selection and reference the Objects directly by Name or Index.