1

I'm using Excel 365 and when I try to refresh a set of PivotTable using Refresh All I get the following message:

Unable to refresh data types

I have read that this is to do with whether or not you have an internet connection, which was off, when I turn it on then it works. However, as a alternative I also have this code to refresh the Pivot Tables:

For Each SheetPivotTables In Sheets("Analysis").PivotTables

    SheetPivotTables.RefreshTable
    
Next SheetPivotTables

Which works regardless of internet availability, I also noticed that if you refresh one table at a time it's fine. Is there a way to resolve the error message?

Improve this question
2
  • What kind of data source did you use? How did you create the connection? Commented Dec 16, 2020 at 9:07
  • It's an Excel table. Even if I create a small table with two columns and use that as the source of the Pivot Table the error still appears. Commented Dec 16, 2020 at 23:38

2 Answers 2

Reset to default
0

Try saving file to “Excel Options > Trust Center > Trust Center Settings > Trusted Locations.”

Add a location wherever you want.

Don't use the locations here. I stored my macro enabled files to one of Excel's trusted locations and opened it and it opens all your Excel files.

So create your own trusted Location.

Improve this answer
0

Right click table tab > View Code.

Click, (General) > Worksheet

To right dropdown click Change.

Type this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim pc In ThisWorkbook.PivotCaches

Next

End Sub

Do this to all your Tables that have PivotTables.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.