Refresh All "couldn't get data from Table8" - but no Pivots reference Table8!

[megera716]

Our budget has many pivot tables. When I click Refresh All, I get an error saying "We couldn't get the data from 'Table8' in the workbook 'M:\2019 Budget.xlsm'.

Table8 doesn't exist (per Name Manager), as you may have guessed. The thing is that there also ISN'T a Pivot Table that references Table8! I'm certain of it. I've run a macro to show all Pivots in the workbook - nothing that references a Table8. I've even gone one by one through all the Pivots, individually refreshed every single one and clicked "Change Data Source" - no errors, none reference Table8. Where is this phantom Pivot Table?? It's driving me batty.

 

[Twollaston]

Did you try going to the data tab and checking your connections?

 

[megera716]

Well, I did now! :P

Sure enough, under Queries and Connections, it says "WorksheetConnection_2018 Budget.xlsm!Table8.

So I went and opened up our 2018 Budget and Table 8 isn't in there either under Name Manager nor does the 2018 Budget have any Pivots that reference Table 8, per my aforementioned macro. When we created the 2019 Budget, we just copied the 2018 Budget file and renamed it, so it makes some sense that this is lingering from the old file.

I right-clicked on the connection in the 2019 Budget and clicked Delete and it came up with a message that says "Removing connection(s) will separate this workbook from its data source(s), and data refresh operations in the workbook will no longer succeed. Click Cancel to keep connection(s) or click OK to proceed with connection removal."

If I delete it, will it break every other Pivot Table or just the non-existent Pivot Table referencing Table8? I can also go into Properties and just tell it not to refresh this connection when I click Refresh All. But if all my other Pivots will be safe and deleting it will only not allow refresh of the non-existent Pivot Table that references Table8, I'd rather just delete it.

 

[Twollaston]

Well, I did now! :P

Sure enough, under Queries and Connections, it says "WorksheetConnection_2018 Budget.xlsm!Table8.

So I went and opened up our 2018 Budget and Table 8 isn't in there either under Name Manager nor does the 2018 Budget have any Pivots that reference Table 8, per my aforementioned macro. When we created the 2019 Budget, we just copied the 2018 Budget file and renamed it, so it makes some sense that this is lingering from the old file.

I right-clicked on the connection in the 2019 Budget and clicked Delete and it came up with a message that says "Removing connection(s) will separate this workbook from its data source(s), and data refresh operations in the workbook will no longer succeed. Click Cancel to keep connection(s) or click OK to proceed with connection removal."

If I delete it, will it break every other Pivot Table or just the non-existent Pivot Table referencing Table8? I can also go into Properties and just tell it not to refresh this connection when I click Refresh All. But if all my other Pivots will be safe and deleting it will only not allow refresh of the non-existent Pivot Table that references Table8, I'd rather just delete it.

I'm not 100% sure about how it will react to that. I would save before doing that, and then remove the stale connection and try to refresh the rest of your data and see what happens. Or just save a copy of this workbook, in case it doesn't work.