Filtering pivot tables

[Darren]

Hi,

I have a pivot table that draws info from an SQL database, I would like to
be able to specify the month that is displayed on the pivot by reference to
a cell on the same tab.

In other words can I influence the boxes that are ticked within the drop
down filters for the pivot table.

In other, other words, if the data contains sales over a period of months
can I default the 'Month' field to be current month without disabling the
ability to change the selection in order to show any/ all months.

Is it possible in VBA to specify the filtering of a pivot table?

Daz

 

[Macgru]

Hi,

I have a pivot table that draws info from an SQL database, I would like to
be able to specify the month that is displayed on the pivot by reference to
a cell on the same tab.

In other words can I influence the boxes that are ticked within the drop
down filters for the pivot table.

In other, other words, if the data contains sales over a period of months
can I default the 'Month' field to be current month without disabling the
ability to change the selection in order to show any/ all months.

Is it possible in VBA to specify the filtering of a pivot table?

Daz

try to modify this routine:

currMonth = range("a1").Value

With Worksheets("sheets1").PivotTables(1)
On Error Resume Next
For Each pvtf In .PivotFields
If pvtf.Name = "month" Then
For Each pvti In pvtf.PivotItems
pvti.Visible = pvti.Name = CStr(currMonth)
Next
End If
Next

End With

mcg

 

[Darren]

Hi,
thanks for that, mekes sense.

how do I go about doing this? do I create a VBA project for this?

Darren