AutoRefresh 5-7 Pivots within a Workbook. Is it possible?

I’m back again! After Grand success of my first post. All thanks to OllieB. I’m pretty excited to be a part of this Forum…

From my Research, I was able to find the attached Macro that Auto Refreshes Data. I have a request to be able to customize this or have an alternative solution and was hoping to get some expert help….

Here’s my scenario:

I have to create 25 reports daily with 5-7 separate Pivot tabs. The entire data per report for each of the Pivots is pulled in from dedicated tab within each report.

Have also attached a dummy data. Would really appreciate if anyone can help me with a direction on how this can be done and if it is possible? I need to draw Pivots using several columns and combinations.

I understand that the pivots can be refreshed but my problem is that the percentages (example in Column D of the Pivot 1 tab) of the attached gets affected each time I refresh the pivots after changing data. The main thing that I need in those pivots is the flexibility to use the sub-totals to calculate percentage by pivots.

Hopefully, someone can help me out solve this challenge!

Thank you!

I have no experience with pivot tables but managed to add a percentage column to the pivot table itself so any refresh will update this column properly. Does this help in any way?

Sir,

I did check this out. My outcome on refreshing is not coming exactly the way it should…

For example: In case of Corporation Name - D Row 6. I delete the Row and Refresh the Pivots. The percentages are changing incorrectly i.e.

• Acquired is changing from 66.67% to 100%
• Sold is Changing from 33% to 100%

It should be changing from:

• Acquired is changing from 66.67% to 50%
• Sold is Changing from 33% to 50%

I would like the percentages to be refreshed based on the changes in the Sub Total.

Happy to post an Excel example, if you suggest.

Thx

spiwere,

I did the same in the version I posted to you and when I click refresh the pivot table gets refreshed properly, i.e. both percentages are shown at 50. I am sure you deleted the row from the Data Base worksheet, right?

I have attached my version so you can compare where it (possibly) went wrong.

I am not sure what you are trying to say. I tested what you claim that did not work in post # 9 and reverted in post #10 that I had no problems. The percentage values were updated correctly as shown in the workbook I provided. As far as I am concerned the pivot table shows correct percentages also when you add or delete rows. In conclusion I do not understand your problem

Hi Spi and Ollie

The last attachment from Ollie needs a Dynamic Named Range spanning all the data. Then this Name is used in the Pivot Table's data source. That will make the Pivot Table reflect the span of the data dynamically. BUT - you also need some VBA Event code to refresh the Pivot Table whenever any of the data changes. That is done with VBA code.

I don't see any Named Ranges or VBA in Ollie's last attached example.

Perhaps that is what is missing??

Hi Marvin,

I have only looked at adding a percentage column and have advised spiwere to break down his requests in separate and explicit steps, i.e. only look at the VBA refresh once the pivot tables are fine and post the VBA question in the VBA section of the forum. As mentioned before I have very little knowledge about using pivot tables.

Automatically updating a pivot table (or lots of them) takes some tricks.

The pivot table needs to be based on a Dynamic Named Range of data so when the data changes the Pivot can reflect the growing dataset.
It also needs to be triggered by some VBA Event code to Refresh the Pivot Table.

I show how to do this is the first attachment Spi gives in this thread.

I guess the answer to your question lies in learning Dynamic Named Ranges and Events in VBA. Then use the example to see how they fit together.

Post your phone number and we might call...

I should have said to Private Message me with your Phone number and we can go over what I've done in the example.

Hey spiwere,

The idea here is that I've answered about 6000 questions and don't give out my email or phone so I can answer questions when I feel like it. This is a non-paid labor or love. If you really want help then you will need to PM ME YOUR phone. I would be able to figure out if you know about Dynamic Named Ranges and how to use a Named Range for a Pivot Table data and if you can do an Event Macro. These three topics are important to do the problem you want to solve. I haven't seen a PM from you yet, which makes me wonder if you really do want an answer. My assumption is you are in Indiana USA. Right?