PDA

View Full Version : Solved: AutoRefresh 5-7 Pivots within a Workbook.



Spiwere
01-24-2013, 12:05 AM
Hi Dears VBA Experts,

I’m quite excited to join this Forum. And hope to get some great learning and assistance as well.

This being my first ever post on this Forum, so I hope you’ll excuse me for anything that I may not do correctly. The thing that brings me to this forum is a post by Zack Barresse.

I’m currently exploring for a similar solution to cater to my daily report preparation requirement. In an effort to get some assistance, I had even posted it on Excel Help Forum.

I tried to post the link but got a message that I can't post links until my post count is 5. This is my first post.

I have got some insights, but guess that would need further assistance being and absolutely fresher to the VBA World. Thought It to seek assistance here while I learn., and also after seeing Zack’s post.

To summarize my requirement:

Purpose: I need to create 25 reports for my management review daily. These have some data from the work done by mid-level folks.

Challenge: This takes about 1-hour per report per person at this stage. Immensely time consuming for the 2 of us working on it.

Request: Need your expert assistance to be able to cut down on the time and add at the same time ensure accuracy.

Requirement:

· As a first step, I create 5-7 Pivots in each report based on a dedicated data tab within each excel
· I need to be able to add a % column within each Pivot that calculates the percentage based on “Sub-Totals”
· These % ‘s should be able to get Auto Refreshed based on any change (Addition / Deletion) in the Source data as a result of any changes that occur in the “Sub-Totals”. These get affected whenever I do a manual refresh.


I’ve attached a sample of my report as well with 1 Pivot. I need to create up to 7 Pivots in each report all with % columns.

Hope the above summarizes my request. Look forward for some great help on this..

Happy to share more details and clarify if there are any questions.

Thank you in advance,
Spi


PS: Do let me know if I'm doing something incorrectly. I'll be happy to make amends!

Jan Karel Pieterse
01-24-2013, 12:49 AM
You can drag the field that is in the summary area to that area once more and set it up to show %.

Spiwere
01-24-2013, 01:16 AM
Thanks Jan.

I don't think that's going to work or If I understand it correctly.:dunno

My request is to be able to "Auto Refresh" all the 7 Pivots along with the % values in Column D based on Sub-Totals.

My understanding is that It would required a Dynamic Data Range and a VBA Event to do that, but the irony is that I don't know how to get those incorporated.

I would like to be able to Auto Refresh Pivot data along with %ages based on any changes in the Data Sheet. I technically have up to 7 Pivots based of several data columns. Each Pivot needs to be represented in a seprate worksheet.

Please advise.

Thanks,
Spi

Jan Karel Pieterse
01-24-2013, 02:24 AM
You can set up each pivot table to refresh on workbook open. If you convert each source range to a table (Format as table) and then set up the source for the pivot table to that table, resizing is handled for you automatically.
You can change what is calculated for a field in a pivot table by right-clicking it and selecting "Show Values as". If you need two different calculations on the same field (e.g. Sum and Count) then you can drag that same field to the PT a second time and change its ShowValue as property.

Spiwere
01-24-2013, 02:38 AM
Thanks Jan.

I think I understand what you are suggesting. Would you be kind and able to show that to me by using the sample file I posted?

I know how to run Pivots but the only problem is that with change of data my % calculations is getting srewed up. Perhaps, I missing a trick here.

Thanks for your help!

Best,
Spi

Jan Karel Pieterse
01-24-2013, 04:07 AM
See attached. I also set up the PT to refresh on workbook open.

Spiwere
01-24-2013, 05:15 AM
Hi Jan,
I see % values in Column C of the value you shared. I formatted them to showNumbers and they seem look okay!

However, the % values for D, F and H corporations seem to be off

Corporation Name D. Acquired & Sold should be = 50%. It is 100% each in yourversion
Corporation Name F. Merged should be 100%. It is showing 200%
Corporation Name H. Merged & Sold should be 67% and

Please advise if I’m missing out on anything!

Spiwere
01-26-2013, 10:39 AM
Hello Everyone,

I'm still stuck on this one. Could one of you please assist!

I'll be grateful. Please help me accomplish my first post here...

Best,
Spi

Jan Karel Pieterse
01-26-2013, 12:04 PM
I had not noticed that column C was formatted as percentages. I formatted them as numbers and now it correctly shows the count, and next to it the relevant percentage.

Spiwere
01-28-2013, 07:54 PM
Thanks Jan. This seems okay!

But I really need a VBA solution. I tried recording it, but did not work most likely because I may not be doing it the right way:(

Considering the humungous data and the frequency along with quantity makes me really seek assistance for a VBA.

I'm an absolute newbie so apologies if my request seems to be a
bit unreasonable.

Look forward to some assistance in this regard.

Thanks a lot:)

Jan Karel Pieterse
01-28-2013, 11:24 PM
Considering the humungous data and the frequency along with quantity makes me really seek assistance for a VBA.
Can you explain a bit more about this? I fail to understand the need for VBA here?

Spiwere
01-28-2013, 11:37 PM
Sure Jan,

Here are the details:

1) I create at least 25 such reports daily. This takes me more than1-hour per report.
2) I know that 2010 has the ability to calculate % based on parent sub-totals, but I'm not in a postion to switch to that version
3) I also need to color the rows within Pivots so need to have a VBA that can help me accomplish that with a Key.

My data is usage and takes a lot of time for report generation. Hence, I'm seeking help in this regard.

Happy to share more details. Please advise your thoughts...

Best reagrds,
Spi

Jan Karel Pieterse
01-29-2013, 12:59 AM
Which Excel version do you have? The % calc has been available for quite a number of versions AFAIK.

Spiwere
01-29-2013, 01:07 AM
That's correct! I know for a fact it is available in 2010. However, I'm using a 2007 version with unfortunatley no probability to upgrade, which is why I'm seeking help on it.

Thanks again:)

Jan Karel Pieterse
01-29-2013, 05:17 AM
I'm sure it is in 2007 as well, as it is available in Excel 2003 too. I bet even Excel 5 (1995) had it too.

Spiwere
01-29-2013, 06:00 AM
Toing! I don't know if that is the case. You are an expert so I believe so.

However, my preliminary Research doesn't show that up. Perhaps, I missing something.

Request you to refer to the last 2 posts on this link: http://social.technet.microsoft.com/Forums/nl/excel/thread/3422e880-667a-465d-b97b-ad9ac89d08ae

Please advise.

Jan Karel Pieterse
01-29-2013, 08:13 AM
I stand corrected. This particular percentage feature was indeed added with Excel 2010. Sorry for wasting your time!
I have attached your example file, this time with three columns with formulas.
After refreshing the pivot table all it takes is updating the three formula columns so they point to the proper cells inside the PT. Make sure you use direct cell addresses, not the GetPivotData fuction.

Spiwere
01-29-2013, 08:33 AM
No probs at all. I'll give it a shot and see how this works. The only apprehension is the amount of work I have and don't know if this will be easy to execute considering so many reports and even more pivots in them, which is the reason I'm looking for a VBA / UDF solution.

Nevertheless, thanks for this imense help. I know this is being done without any personal interests. I have learned a lot.

You guys are amazing!!!
Thanks again:)