VBS Help to Auto Refresh Pivot Tables when data or specific cell values change

Hi kwoltman,

Put this code behind your "Pivots" Sheet.

Please Login or Register  to view this content.

Every time you click on the Pivots Tab it will refresh all the pivots in the whole workbook. You don't want to refresh the pivots when you change a cell in the data tab. You wait until someone actualy looks at the pivot sheet.

Make sense? If not then keep asking.

Hi,

The code above, put behind each worksheet that has a pivot table on it should do the trick. The code says - when you activate this tab (click on the tab at the bottom) you need to refresh all tables and pivot tables in the whole workbook. If you put the code behind only one worksheet that has Pivots on it, you need to put the same code behind the other worksheet that has pivots.

This works for when I change or add data entries to the "Data" worksheet. But it doesn't help with the 3 cells on the "Summary" tab.

Assume that my Data worksheet is good and the user is working on the "Summary" worksheet. On the "Summary" worksheet the user can change 3 things (all 3 of these changes are independent of each other, each one DOES change data values on the table on the "Data" worksheet, but in this case the user doesn't have to move between worksheets). For instance, cell B3 has 2 choices in the drop-down: Calendar Year and Fiscal Year. When the user changes this, the data on the "Data" worksheet (column H) changes from showing 2017 (for dates between 1/1/2017-12/31/2017) or FY18 (for dates between 10/1/2017-9/30/2018). When I manually refresh my pivot table it changes what and how I see the info without ever leaving the "Summary" worksheet. But I want to automate this part too.

So I was hoping, in addition to what you provided already, to have VB code that would also auto update the pivot tables and graphs whenever any 1 of these 3 cells (B3, B6, and B10) change when a user is on the "Summary" worksheet and may or may not leave this worksheet.

I hope this is more clear.

OK then,

Put this code behind your Summary sheet and see if this does what you need.

Please Login or Register  to view this content.

The above event code says if you change anything in the B1, B6 or B10 cells it will refresh the tables and pivots in the Active workbook.