How to get a "dynamic" Pivot Table

**John99** · 09-21-2009, 10:59 AM

Hi!

I am doing a rolling forecasting of an income margin of a certain product. I have all the data in one table with column names like September 2009, October 2009 etc.

I am summarizing the data with a pivot table and then I have a "Sum of September 2009", "Sum of October 2009" etc. values as columns.

I have forecasting date and if it is for example 1.9.2009 the column names in my data table are starting from September 2009 to August 2010 (if I am doing the forecasting for a year).

If I change the forecasting date to 1.10.2009 and refresh my pivot table I have a "Sum of October 2009" as a first column. So it is working as it should so far. However, the last column is still "Sum of August 2010" which it was before refreshing. So know I have data for only 11 months because the last column should be "Sum of September 2010".

How could I so that when I am refreshing the pivot table, Excel would understand to roll the months. So that I would always have 12 months of data in my pivot table..?? Do I have to do it with VBA somehow?? To check what is the first column in my pivot table and then get the next 12 months as columns..

Thanks for any help!

Br,
John

**DonkeyOte** · 09-21-2009, 11:04 AM

Might be an idea to post a sample workbook.. I think I follow in so far as you're saying the headings on source sheet are dynamic but the PT is not updating all data field headings per your source sheet, is that correct ?

**John99** · 09-21-2009, 01:45 PM

DonkeyOte,

Thanks for your reply. I have a VBA to update the headings in a source sheet when "Date" is changed. I have attached a harsh sample of my case. I hope it helps.

So when I change the date from 1.9.2009 to 1.10.2009 the first column in PT is changed to "Sum of October 2009" as it should be but the last column is still "Sum of August 2009" even though i would like it to adjust accordingly. On other words, to be "Sum of September 2009"

**DonkeyOte** · 09-22-2009, 03:53 AM

What happens is that the column that was originally part of the PT is no-longer and thus is deactivated ... at the same time a new column is added but this was not part of the original PT and so is not active...

Using your example where you have date set as Sep-09 and thus 12 months to Aug-10 active on the PT... you alter Sep-09 to Oct-09... at this point Sep-09 field is no longer active and is thus removed from the PT Field List, a new month is created at source (by means of altering header) namely Sep-10 but this was not part of the original PT and thus is not visible... ie field count has dropped from 12 fields to 11 fields. If you were then to change Oct-09 to Nov-09 you will find that field count drops to 10 - Sep-09 & Oct-09 have been removed, replaced in the field "listing" by Sep-10 & Oct-10 but neither of these are "active" PT Data Fields.... does that make sense ?

In essence when you alter the start month you need to update the PT to ensure the "new" month is added to the data field region of the PT.

In terms of the VBA... if you remove all of your existing code in Module1 and replace the PT change event code with something like:

Please Login or Register  to view this content.

You will encounter additional issues (in terms of positioning) if you go "backwards" in time or jump forwards by more than 1 month so I suspect you will need to make this far more robust long-term but hopefully it points you in the right direction / gives you a way forward.

**John99** · 09-22-2009, 07:31 AM

DonkeyOte,

Thanks for your code, it seems nice! However, when I used it the line

Please Login or Register  to view this content.

Activates only the new data field and deactivates the other..? And I have a PT with only one column. I don't know why this is happening though. Do you?

I think I can at the moment cope with the fact that I can only change one month at a time. But I would like it to be possible to change the date one month backwards. So I tried to solve this by using ChangeInMonths variable: if it is positive it works as your code and if it is negative it should add a new data field as a first column in a PT. If it is bigger than 31 code will produce an error message.

So this is when a new problem comes along.. how can I add a new data field in to the first column if the ChangeInMonths is negative..?

-John

Please Login or Register  to view this content.

**DonkeyOte** · 09-22-2009, 07:52 AM

Based on your last file, perhaps easier just to iterate the cells in the table and progress from there.

Please Login or Register  to view this content.

**John99** · 10-19-2009, 11:55 AM

DonkeyOte,

I appreciate your thoughts on this one. It took some time to get back to this case. I tried to use your latest code but I wasn't able to get it working. The code doesn't want to go through from this method: ".PivotCache.Refresh" and I am getting a run-time error as an result saying that "Reference is not valid". Any thoughts what might cause this problem..?

Thanks so much for your help!

**DonkeyOte** · 10-19-2009, 12:03 PM

Please post a sample file in which the code provided is failing.
(presumably your Pivot Table is called PivotTable3 - and if not you've adjusted accordingly ?)

Incidentally (as I see it) you shouldn't be getting an Error Message with the code provided, the Errors are handled to the extent the routine is simply terminated.