hi there,

I am not a VBA user and got code from a book to generate a pivottable from a
macro. Now the problem is I want to assing the filter on column header '
Period' in the code and don’t know which property to change. following is the
code of the macro

Sub CreatePivotFields()

ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="'Source'!R1C1:R73C7", _
TableName:="Sales&Trans"

With ActiveSheet.PivotTables("Sales&Trans")
..PivotFields("Year").Orientation = xlPageField
..PivotFields("Year").Position = 1
..
..
..PivotFields("Period").Orientation = xlColumnField

at this point i want to assign a filter to 'Period' so it only display
period 'one' items in the pivot.

thanks,

Try
I don't know how many items you have, you may have to adjust.

..PivotFields("Period").Orientation = xlColumnField
With ActiveSheet.PivotTables("Sales&Trans").PivotFields("Period")
.PivotItems("1").Visible = True
.PivotItems("2").Visible = False
.PivotItems("3").Visible = False
.PivotItems("5").Visible = False
End With
Skinman

"Atiq" <(E-Mail Removed)> wrote in message
news:F2C13BAE-F5B7-4F4D-A470-(E-Mail Removed)...
> hi there,
>
> I am not a VBA user and got code from a book to generate a pivottable from
> a
> macro. Now the problem is I want to assing the filter on column header '
> Period' in the code and don’t know which property to change. following
> is the
> code of the macro
>
> Sub CreatePivotFields()
>
> ActiveSheet.PivotTableWizard _
> SourceType:=xlDatabase, _
> SourceData:="'Source'!R1C1:R73C7", _
> TableName:="Sales&Trans"
>
> With ActiveSheet.PivotTables("Sales&Trans")
> .PivotFields("Year").Orientation = xlPageField
> .PivotFields("Year").Position = 1
> .
> .
> .PivotFields("Period").Orientation = xlColumnField
>
> at this point i want to assign a filter to 'Period' so it only display
> period 'one' items in the pivot.
>
> thanks,
>
>

thanks Skinman, it worked.

now another question , is it possible to give excluesive code, i mean can
i put code to say that show "1" and hide all others without needing to know
the complete list of values in that pivot item.

thanks again.

"Skinman" wrote:

> Try
> I don't know how many items you have, you may have to adjust.
>
> ..PivotFields("Period").Orientation = xlColumnField
> With ActiveSheet.PivotTables("Sales&Trans").PivotFields("Period")
> .PivotItems("1").Visible = True
> .PivotItems("2").Visible = False
> .PivotItems("3").Visible = False
> .PivotItems("5").Visible = False
> End With
> Skinman
>
> "Atiq" <(E-Mail Removed)> wrote in message
> news:F2C13BAE-F5B7-4F4D-A470-(E-Mail Removed)...
> > hi there,
> >
> > I am not a VBA user and got code from a book to generate a pivottable from
> > a
> > macro. Now the problem is I want to assing the filter on column header '
> > Period' in the code and don’t know which property to change. following
> > is the
> > code of the macro
> >
> > Sub CreatePivotFields()
> >
> > ActiveSheet.PivotTableWizard _
> > SourceType:=xlDatabase, _
> > SourceData:="'Source'!R1C1:R73C7", _
> > TableName:="Sales&Trans"
> >
> > With ActiveSheet.PivotTables("Sales&Trans")
> > .PivotFields("Year").Orientation = xlPageField
> > .PivotFields("Year").Position = 1
> > .
> > .
> > .PivotFields("Period").Orientation = xlColumnField
> >
> > at this point i want to assign a filter to 'Period' so it only display
> > period 'one' items in the pivot.
> >
> > thanks,
> >
> >
>
>