Dates in Pivot Tables
 

Hi clever people. I am currently doing a project where I am using VB to
produce pivot tables and charts for the end user via macro buttons etc, so
that they don't have to manipulate the pivot tables for themselves (some
people find them confusing!). Ideally what I would like to do is allow users
to input a "From" and "To" date before the macro is run, so as to only return
the data into the pivot that they want to see based on the time periods.
Does anybody know if this is possible. I have tried the "grouping" option
but that seems to be a bit hit and miss and doesn't seem to work with this
set of data I am using.

Any pointers in the right direction gratefully received!

Dates in Pivot Tables
 

Zani,

Try something like this.

Sub DataRange()

fromdate = InputBox("from")
todate = InputBox("to")

cnt =
Activesheet.PivotTables("PivotTable1").PivotFields ("DATE").PivotItems.Count

For i = 1 to cnt
With Activesheet.PivotTables("PivotTable1").PivotFields ("DATE")
CHK = .PivotItems(i).value
Select Case CHK
Case fromdate To todate
.PivotItems(i).Visible = True
Case Else
.PivotItems(i).Visible = False
End Select
End With
Next i
End Sub

Regards,
Mike
"Zani" wrote:

Hi clever people. I am currently doing a project where I am using VB to
produce pivot tables and charts for the end user via macro buttons etc, so
that they don't have to manipulate the pivot tables for themselves (some
people find them confusing!). Ideally what I would like to do is allow users
to input a "From" and "To" date before the macro is run, so as to only return
the data into the pivot that they want to see based on the time periods.
Does anybody know if this is possible. I have tried the "grouping" option
but that seems to be a bit hit and miss and doesn't seem to work with this
set of data I am using.

Any pointers in the right direction gratefully received!