Hi Rajiv,

You have only put the first piece of code in your file. You're missing the second code that should be in the 'ThisWorkbook' object for the Sub RefreshPT()...

If you check the sheet called 'Refresh VBA' in my 'completed' file for lesson 2 you'll see the code.

Mynda

Hi Mynda,

Thanks a lot for your quick response. I understood where I went wrong - included the 2nd code in the .xlsx and not in the .xlsm version (my bad!). However to the second part of my question - Post this code, will the dashboard tab get auto refreshed whenever new data is added?

Regards,

Rajiv

Glad you figured it out, Rajiv.

The code will be triggered to refresh all PivotTables when you change focus from the sheet containing the source data to any other sheet in the file. The assumption is that if you're on the source data sheet then you might be updating it, therefore when you select another sheet in the file you'll want to refresh all of the PivotTables to pick up the changes in the source data.

Hope that makes sense.

Mynda

Thanks a lot Mynda. Your explanation was helpful.

Regards,

Rajiv

Hi Mynda and Phillip, 

Here's a puzzler: 

I've used the new VBA (updated version)  code. 

1. But...after applying the code properly... it appears that I cannot copy the "new data" and paste it below the source data to expand the dataset... and watch the VBA do its "magic". (I could do this before creating the code and doing a refresh).

2. When I delete 2020 transactions from the "source data" and return to the Dashboard page, yes, the charts update ... but... the slicer still displays (albeit shaded) the 2020 option button. 

Is this the "expected" behavior?

Thanks... 

(All this VBA "trickery" seems to pale in comparison to the interactivity in Power BI! )

Hi Philip,

Please share your file and exact steps so we can reproduce the first issue and troubleshoot.

As for the second issue, PivotTables retain items deleted from the source data in the Pivot Cache by default. If you don't want it to do this you can go into the PivotTable Options > Data tab > set 'Retain items deleted from the data source' to None.

Mynda