Another simple ( I hope) question

[Angelyne]

You have been so helpful to me already. I have another question and this is going to make my spreadsheet a zillion time better

This is my code. I took it from a helpful website. When I enter the pf.CurrentPage value directly into the code, (as below) this works perfectly. But what I really want is to set pf.CurrentPage to the value of a formula in C3. I tried doing pf.CurrentPage = "C3" but that gave me an error. That formula doesn't seem to work for some reason. Any thoughts?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'PURPOSE: Filter on a single item with the Report Filter field
'SOURCE: www.TheSpreadsheetGuru.com

Dim pf As PivotField

Set pf = ActiveSheet.PivotTables("PivotTable2").PivotFields("Cost Centre (Existing)")

'Clear Out Any Previous Filtering
pf.ClearAllFilters

'Filter on cost center
pf.CurrentPage = "3452"


End Sub

 

[Logit]

Does this work ...

pf.CurrentPage.value = "3452"

 

[Angelyne]

Yes it works. It doesn't work when I tried to use pf.CurrentPage.value = "E3" (Where E3 value is 3452)

 

[Logit]

Great.

 

[Angelyne]

But I need the formula to use the value in the cell E3, otherwise, it's just hard-coding the filter value in the code, which is useless to me

 

[Logit]

I had hoped inserting ".value" would have done the trick. Sorry I can't be of further assistance.

 

[Angelyne]

Yes!! figured this out. Adding the code in bold made this code works :

Dim pf As PivotField

Set pf = ActiveSheet.PivotTables("PivotTable2").PivotFields("Cost Centre (Existing)")
NewCat = Worksheets("Inventory").Range("C3").Value

'Clear Out Any Previous Filtering
pf.ClearAllFilters

'Filter on cost center
pf.CurrentPage = NewCat

 

[Logit]

Excellent !!!


It's always better when the solution comes from your brain instead of someone else's >>>>>

 

[Angelyne]

thanks for the help