Run-time error '424': on Pivot Refresh

programsam

Board Regular
Joined
Feb 10, 2016
Messages
123
I have a sub that refreshes a series of object tables, queries tables and Pivot Tables and when it transitions to the Pivot section, it triggers a "Run-time erorr '424': Object required" and it does not give me the option to debug. When I close out the prompt, Excel then tries to close out the file. If I go in to Visual Basic and try to iterate through the code line-by-line, I get no errors.

I added in the Application.StatusBar code ahead of every table refresh so that, while running automated, I could pinpoint where exactly the error is occuring and it seems to trigger in this area...

VBA Code:
wb.Application.StatusBar = "ACTIVATING PIVOT TABLES"
wb.Application.StatusBar = "Refreshing SHIPPED Pivot Table..."
wb.Sheets("SHIPPED").Select
wb.ActiveSheet.PivotTables("SHIPPED").PivotCache.Refresh

I'm stumped, thoughts?

VBA Code:
Sub refreshTables()

Dim wb As Workbook

Set wb = ThisWorkbook

wb.Application.StatusBar = "Activating QUERIES..."

wb.Application.StatusBar = "Updating Shipped Orders TABLE..."
wb.Sheets("SHIPPED_ORDERS").Activate
wb.Sheets("SHIPPED_ORDERS").ListObjects(1).Refresh
Application.CalculateUntilAsyncQueriesDone

wb.Application.StatusBar = "Updating New Orders TABLE..."
wb.Sheets("NEW_ORDERS").Activate
wb.Sheets("NEW_ORDERS").ListObjects(1).Refresh
Application.CalculateUntilAsyncQueriesDone

wb.Application.StatusBar = "Refreshing YTD Data QUERY..."
wb.Sheets("YTD_DATA").Activate
wb.Connections("Query - YTD_SALES").Refresh
Application.CalculateUntilAsyncQueriesDone

wb.Application.StatusBar = "Refreshing QTD Data QUERY..."
wb.Sheets("QTD_DATA").Activate
wb.Connections("Query - QTD_SALES").Refresh
Application.CalculateUntilAsyncQueriesDone

wb.Application.StatusBar = "Refreshing MTD Data QUERY..."
wb.Sheets("MTD_DATA").Activate
wb.Connections("Query - MTD_SALES").Refresh
Application.CalculateUntilAsyncQueriesDone

wb.Application.StatusBar = "Refreshing Daily Data QUERY..."
wb.Sheets("DAILY_DATA").Activate
wb.Connections("Query - DAILY_SALES").Refresh
Application.CalculateUntilAsyncQueriesDone

wb.Application.StatusBar = "ACTIVATING PIVOT TABLES"
wb.Application.StatusBar = "Refreshing SHIPPED Pivot Table..."
wb.Sheets("SHIPPED").Select
wb.ActiveSheet.PivotTables("SHIPPED").PivotCache.Refresh

wb.Application.StatusBar = "Refreshing NEW ORDERS Pivot Table..."
wb.Sheets("NEW ORDERS").Select
wb.ActiveSheet.PivotTables("NEW ORDERS").PivotCache.Refresh

wb.Application.StatusBar = "Refreshing SUPS Pivot Table..."
wb.Sheets("SUPERVISORS").Select
wb.ActiveSheet.PivotTables("SUPS").PivotCache.Refresh

wb.Application.StatusBar = "Refreshing SALES Pivot Table..."
wb.Sheets("SALES").Select
wb.ActiveSheet.PivotTables("DAILY_ISR").PivotCache.Refresh
wb.ActiveSheet.PivotTables("MTD_ISR").PivotCache.Refresh
wb.ActiveSheet.PivotTables("QTD_ISR").PivotCache.Refresh
wb.ActiveSheet.PivotTables("YTD_ISR").PivotCache.Refresh

wb.Application.StatusBar = "Refreshing SUMMARY Pivot Table..."
wb.Sheets("SUMMARY").Select
wb.ActiveSheet.PivotTables("ISR_AOV").PivotCache.Refresh
wb.ActiveSheet.PivotTables("SHIP_RANK").PivotCache.Refresh
wb.ActiveSheet.PivotTables("NEW_RANK").PivotCache.Refresh
wb.ActiveSheet.PivotTables("LEADS_BREAKDOWN").PivotCache.Refresh

wb.Application.StatusBar = "Activating BONUS..."
wb.Application.StatusBar = "Refreshing BONUS Table..."
wb.Sheets("BONUS").Activate
wb.Sheets("BONUS").ListObjects(1).Refresh
Application.CalculateUntilAsyncQueriesDone

End Sub



[ATTACH type="full" alt="1706106957111.png"]105631[/ATTACH]
 

Attachments

  • 1706106957111.png
    1706106957111.png
    24.4 KB · Views: 12

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,224,817
Messages
6,181,148
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top