VBA - Using Pivot Table Filter Value as Variable

[vba_monkey]

Hello,

Is it possible to take value selected in a picoit table filter and then set it as a variable in VBA?

So, If someone was to select 2017 in a field filter, take that 2017 and set it as a variable so it can be used elsewhere in the macro.

Thanks

 

[Marcelo Branco]

Maybe something like this - adjust the sheet name, pivot table name and page field name

Dim PVT As PivotTable, myVar As Variant
    
Set PVT = Sheets("Sheet1").PivotTables("Pivot1")
myVar = PVT.PageFields("Year").CurrentPage
MsgBox myVar

M.

 

[vba_monkey]

Thanks.

That only returns (All), even with only one item selected

 

[Marcelo Branco]

Thanks.

That only returns (All), even with only one item selected

Worked perfectly for me.

M.

 

[Marcelo Branco]

Complementing my previous post
You should uncheck the option
Select Multiple items

M.

 

[vba_monkey]

Thank you, now it works.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.