Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to filter the data inside the series command? | Charts and Charting in Excel | |||
Event Macro running another macro inside | Excel Discussion (Misc queries) | |||
How to assign same code inside Option button code space ?? | Excel Programming | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Need macro to filter, create tab on filter and copy/paste | Excel Programming |