How to get a "dynamic" Pivot Table

[John99]

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]

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]

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]

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:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, PT As PivotTable, strDate As String
On Error GoTo ExitPoint
If Target.Count > 1 Or Intersect(Target, Range("Date")) Is Nothing Then Exit Sub
If IsDate(Target.Value) Then
    strDate = Format(DateAdd("m", 11, Target.Value - Day(Target.Value) + 1), "MMMM YYYY")
    For Each Cell In Sheets("Data").Range("Dates")
        Cell.Offset(1).Value = Format(Cell.Value, "MMMM YYYY")
    Next
    Application.EnableEvents = False
    Set PT = ActiveSheet.PivotTables("PivotTable3")
    PT.PivotCache.Refresh
    With PT: .AddDataField PT.PivotFields(strDate), "Sum of " & strDate, xlSum: End With
ExitPoint:
    Set PT = Nothing
    Application.EnableEvents = True
End If
End Sub

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]

DonkeyOte,

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

With PT: .AddDataField PT.PivotFields(strDate), "Sum of " & strDate

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

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, PT As PivotTable, strDate1 As String, Period As Range, ChangeInMonths As Integer, FirstDate As Range, Dtm As Range

Set Period = Range("Period")

Set FirstDate = Worksheets("Data").Range("FirstDate")
Set Dtm = Range("Date")


If Target.Count > 1 Or Intersect(Target, Dtm) Is Nothing Then Exit Sub
End If
If IsDate(Target.Value) Then
    MsgBox "Vaihtuu"
    ChangeInMonths = Dtm - FirstDate
    MsgBox Abs(ChangeInMonths)
    If Abs(ChangeInMonths) > 31 Then
        MsgBox "You can change only one month forward or backward"
        Exit Sub
    End If
    
    
    If ChangeInMonths >= 0 Then
        strDate1 = Format(DateAdd("m", Period.Value - 1, Target.Value - Day(Target.Value) + 1), "MMM YYYY")
        
        For Each Cell In Sheets("Data").Range("Dates")
            Cell.Offset(1).Value = Format(Cell.Value, "MMM YYYY")
        Next
        
        Application.EnableEvents = False
        Set PT = ActiveSheet.PivotTables("PivotTable3")
        PT.PivotCache.Refresh
        With PT: .AddDataField PT.PivotFields(strDate), "Sum of " & strDate, xlSum: End With
    End If
        
    If ChangeInMonths < 0 Then
        strDate1 = Format(DateAdd("m", -1, Target.Value - Day(Target.Value) + 1), "MMM YYYY")
        
        For Each Cell In Sheets("Data").Range("Dates")
            Cell.Offset(1).Value = Format(Cell.Value, "MMM YYYY")
        Next
        
        Application.EnableEvents = False
        Set PT = ActiveSheet.PivotTables("PivotTable3")
        PT.PivotCache.Refresh
        With PT: .AddDataField PT.PivotFields(strDate), "Sum of " & strDate, xlSum: End With
    End If

End If
End Sub

[DonkeyOte]

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

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, PT As PivotTable
If Target.Count > 1 Or Intersect(Target, Range("Date")) Is Nothing Then Exit Sub
On Error GoTo ExitPoint
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
Set PT = ActiveSheet.PivotTables("PivotTable3")
For Each Cell In Sheets("Data").Range("Dates")
    Cell.Offset(1).Value = Format(Cell.Value, "MMM YYYY")
    With PT
        .PivotCache.Refresh 'not ideal
        On Error Resume Next
        .AddDataField PT.PivotFields(Cell.Offset(1).Value), "Sum of " & Cell.Offset(1).Value, xlSum
        On Error GoTo ExitPoint
    End With
Next Cell
ExitPoint:
Set PT = Nothing
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

[John99]

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!