Fermi,
There are a few approaches that you can take. I will suggest two of them here.
First Approach: You can make it so that once you click away from the "Transaction Register" tab, your two pivot tables will update. You will need to go into the navigation pane of your Visual Basic Editor (VBE), and instead of writing code into one of your Modules (which is where you have your code), double click on "Transaction Register" and write in the following code:
What the Deactivate event is saying is that each and every time you click away from the "Transaction Register" tab, the pivot tables (i.e., PivotTable4 and PivotTable1) will update themselves. Run the macro once just to make sure it gets run. Then it should continue to work in the background on an ongoing basis even after you close out of the file and reopen it again.
Second Approach: You can write a macro that will update both pivot tables at once but at a time of your choosing (as opposed to it happening each time you click away from the "Transaction Register" tab). It would look something like this (and you ought place the code in a new module):
Under this second approach, you run the macro when you want to so that the pivot tables are updated at your discretion. Remember, place this code in its own module.
Both approaches should work even with the pivot table tabs hidden. Hope this helps.
Bookmarks