Pivot Tables

Question

Hello, for some reason my pivot tables are not refreshing, if I try to change the data source it get the following message: "Refresh has been disabled by a Visual Basic Macro" however I have no macros! This happened after a recent software update. Can anyone help please? thanks

Answer 1

Please run the macro below once. After that the issue should be gone.

Andreas.

Sub PivotTableEnableRefresh()
  Dim Ws As Worksheet
  Dim Pv As PivotTable
  For Each Ws In Worksheets
    For Each Pv In Ws.PivotTables
      Pv.PivotCache.EnableRefresh = True
    Next
  Next
End Sub

Answer 2

Hi PivotTable_85,

Per the situation you mentioned, the file may help us better understand and try to fix the issue. Could you prepare and upload an example file you mentioned with no confidential/ sensitive data, then share the link here? I’d like to have a test from my side. Also please tell us your Office version number.

Best regards,

Madoc

Answer 3

If you need further help please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

A macro to anonymize data in selected cells can be downloaded here:
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAn...

Please do not use OneDrive, we need a 1:1 copy of the file.

Andreas.

Answer 4

bizarrely i can't get it to work by manually changing the data range by 1 row !!

Answer 5

thanks. i have copied this macro into the record new macro then ran it but unfortunately it didn't work!