PivotTable Macro Filter

[sjack]

Hi,

I would like to be able to filter my pivot table by typing into cell D1 e.g. 111111
This Sub is from me just recording a macro of me manually filtering.

Sub Macro2()
'
' Macro2 Macro
'

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Item].[Item Code].[Item Code]").VisibleItemsList = Array( _
"[Item].[Item Code].&[America]&[111111]")

End Sub


Im guessing itll be something like
"[Item].[Item Code].&[America]&Range.D1")
but just not sure how to get that to work

Hopefully this is enough info, first time doing one of these

 

[sjack]

Hi,

I would like to be able to filter my pivot table by typing into cell D1 e.g. 111111
This Sub is from me just recording a macro of me manually filtering.

Sub Macro2()
'
' Macro2 Macro
'

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Item].[Item Code].[Item Code]").VisibleItemsList = Array( _
"[Item].[Item Code].&[America]&[111111]")

End Sub


Im guessing itll be something like
"[Item].[Item Code].&[America]&Range.D1")
but just not sure how to get that to work

Hopefully this is enough info, first time doing one of these

This is what i did if anyone else wants to use it


ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Item].[Item Code].[Item Code]").VisibleItemsList = Array( _
"[Item].[Item Code].&[Australia]&[" & Worksheets("Control").Range("D1").Value & "]")