VBA Change Pivot Filter Based on Cell Value (Pivot from Database)

[Bench]

Hi,

I'm trying to change the John Smith part of the below vba so that it will change to whatever is in say Sheet1!A1 for arguments sake.

I can find articles for changing it on a standard Pivot Table but as this is coming from a SQL CUBE pivot table i'm struggling to get the correct syntax.

Can anyone help please? Thanks

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Territory].[Territory].[Territory]").VisibleItemsList = Array( _
        "[Territory].[Territory].&[John Smith]")

 

[Andrew Poulsom]

Try:

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Territory].[Territory].[Territory]").VisibleItemsList = Array( _
        "[Territory].[Territory].&[" & Worksheets("Sheet1").Range("A1").Value & "]")

 

[Bench]

Fantastic, been looking to use something like this for ages, thanks alot Andrew.

Try:

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Territory].[Territory].[Territory]").VisibleItemsList = Array( _
        "[Territory].[Territory].&[" & Worksheets("Sheet1").Range("A1").Value & "]")