Refresh all pivot tables automatically when a cell value changes

**ANS** · 07-09-2013, 08:46 AM

Hi all,
hope this is the right forum!

Basically, I'd like to have a way for data in pivot tables to auto-refresh whenever a predefine cell value changes.

What i've done so far is just to add a button to manually refresh them (of course, you can just click on the "refresh all" button from the ribbon... but I want to make it as easy as possible for the end user of the file...).

By the way, what I've done is create check boxes which change values in raw data on which pivot tables run... so what I want to do is to have the pivots auto refresh whenever a box is checked/unchecked.

Your help will be greatly appreciated!

Thanks!

**JosephP** · 07-09-2013, 10:13 AM

how do the checkboxes change the data-linked cells or macro? if via macro you can just add activeworkbook.refreshall to the macro

**ANS** · 07-10-2013, 02:43 AM

Hi
linked cells.
I added a row to the raw data set with a 0 or 1 filter.
I linked the checkboxes to cells, near the cells i placed a unique ID that also appears in the raw data.
the column in the raw data has a vlookup function looking at those values. if they are set to false then the value in the column gets zero, true -> 1.

**JosephP** · 07-10-2013, 03:02 AM

linked cells don't trigger the change event for a sheet but you have a couple other options

assign a macro to the controls that refreshes the pivots
use the activate event of any sheet containing pivot tables to refresh the pivots
use the calculate event of the data sheet to update the pivot tables whenever any of its formulas recalculate

for the last one the code would be

Please Login or Register  to view this content.

**ANS** · 07-11-2013, 04:41 AM

Thanks a lot!
What I ended up doing was to assign a refresh all macro to the checkboxes (each individually)

Sub refreshall()
ActiveWorkbook.refreshall
End Sub

Thanks a lot for your help!