assign filter inside macro
 

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 dont 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,

assign filter inside macro
 

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" wrote in message
...
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 dont 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,

assign filter inside macro
 

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" wrote in message
...
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,