VBA & Macro Problems

Fix Excel VBA Error 91 When Refreshing QueryTables

Maira Mehtab Sep 27, 2026 869 views

Question details

The user is attempting to resolve an Excel VBA run-time error 91 that occurs specifically when executing a macro to refresh QueryTables and PivotTables.

Product: Excel
Device & OS: not provided
Scenario: Running a VBA macro to refresh multiple queries and PivotTables in a workbook containing sensitive cost-center data.
Observed behavior: The macro halts and returns a run-time error 91 on a QueryTable.Refresh statement, even though the exact same VBA code functions properly in other workbooks.

Before you start

Before modifying your VBA code, ensure that all external data connections and network drives referenced by the queries are currently accessible and functioning.

Solution 1Recommended

Check Data Model vs. Excel Table Loading

Use this solution if your query was accidentally loaded to the Data Model, which changes how VBA interacts with the QueryTable object.

VBA QueryTable methods often fail with error 91 if the underlying query is loaded strictly to the Excel Data Model rather than a standard worksheet table. Adjusting the load destination can restore functionality.

Open Queries & Connections

In Excel, navigate to the 'Data' tab on the ribbon and click on 'Queries & Connections' to open the side panel.

Modify Query Load Settings

Right-click the problematic query in the panel and select 'Load To...' from the context menu.

Load to Table

Ensure that 'Table' is selected instead of 'Only Create Connection' or 'Add this data to the Data Model', then click 'OK' and rerun your macro.

Data Model Limitations: If you absolutely require the Data Model for Power Pivot, you must update your VBA script to interact with ModelTable or WorkbookConnection objects instead of standard QueryTables.

Solution 2

Verify Worksheet and Object Names

Ensures that the exact object names defined in your VBA code match the names existing in your current workbook.

Solution 3

Isolate the Issue in a Sanitized Workbook

Helps determine if file corruption or background data structure issues are triggering the error.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

If you frequently encounter complex macro errors or slow query refreshes in Microsoft Office, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet environment that handles complex data with ease.

1. Download WPS Office: Visit the official WPS website to download and install the free software suite.
2. Open Your Workbook: Launch WPS Spreadsheets and open your existing .xlsm files directly.
3. Run Macros: Navigate to the Developer tab to seamlessly execute your VBA scripts within WPS.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv)Supports robust VBA and Macro execution in WPS Office ProLightweight installation with faster load times for large datasetsFamiliar tabbed interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

What does VBA run-time error 91 mean?

Run-time error 91 indicates that an 'Object variable or With block variable not set'. In the context of QueryTables, it means the VBA code cannot find the specified table, query, or worksheet object you are trying to refresh.

Can I refresh all PivotTables at once using VBA?

Yes, you can refresh all PivotTables in a workbook by using 'ThisWorkbook.RefreshAll', or by looping through the PivotTables collection on each worksheet and applying the '.PivotCache.Refresh' method.

Why does my macro work in one workbook but throw error 91 in another?

This discrepancy usually occurs because the failing workbook has different sheet names, missing QueryTables, invisible trailing spaces in object names, or queries that were loaded strictly to the Data Model instead of standard worksheets.

Excel & Data Problems