Filtering pivot tables

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

Użytkownik "Darren" <[email protected]> napisał w wiadomości
news:[email protected]...
> 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

Hi,
thanks for that, mekes sense.

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

Darren
"Macgru" <[email protected]> wrote in message
news:[email protected]...
>
> Użytkownik "Darren" <[email protected]> napisał w wiadomości
> news:[email protected]...
>> 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
>
>