Filter two pivot tables in one sheet based on a cell value

[katekoz]

Hello,
I'm trying to create a macro that will filter two different pivot tables in one sheet based on a value in cell A1 of the same sheet. I have some code earlier in this macro that's working fine, so I don't show it here. Below is what I've written to filter the first pivot table in the sheet. I was planning to add the second when I work out the first, which I haven't yet.

Some info - the field I'm filtering for is called "User ID" and I'm filtering for a the value (the user's id) in A1. My Pivot Tables may not always have the same names, so I can't reference the tables by name. I believe you can use the phrase "PivotTables(1)" and "PivotTables(2)" to reference the two tables in the sheet. I tried this is in code earlier and it worked, so I am trying to use that again here. I'm getting an Object Code error on my PTF=... line.

Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem
Dim PTF As String

'Filter the two pivots for the Username
Set PT = ActiveSheet.PivotTables(1)
Set PF = PT.PivotFields("User ID")
PTF = ActiveSheet.Range("A1").Value

With PT
PF.ClearAllFilters
PF.CurrentPage = PTF
PT.RefreshTable
End With

Thanks for any help/suggestions you can offer - even if it's an entirely different approach than I'm taking here!
Kate

 

[DanteAmor]

Try this

Sub test1()
  Dim PT As PivotTable, PF As PivotField
  Dim PTF As Variant
  
  PTF = ActiveSheet.Range("A1").Value
  For Each PT In ActiveSheet.PivotTables
    Set PF = PT.PivotFields("User ID")
    PF.ClearAllFilters
    PF.CurrentPage = PTF
    PT.RefreshTable
  Next
End Sub

 

[katekoz]

I made the changes. I now get an error of "Application-defined or object-defined error" on the PF.CurrentPage = PTF line.

 

[DanteAmor]

Hi and welcome to the forum!

First, the "User ID" filter must be within Filters Area
Second, the table must have at least one ID that matches your value in cell A1.

You can put a picture of your data. (If the workbook contains confidential information, you could replace it with generic data.)

 

[katekoz]

Of course! I was using test data for a user that wasn't even in the data set! I used one that was in there, but not in my original set, and it worked. Thank you so much!
Could you please tell me how you knew to set PTF as a Variant?

 

[DanteAmor]

I'm getting an Object Code error on my PTF=... line.

Could you please tell me how you knew to set PTF as a Variant?

I really don't know why you have an error with the As String statement, for me it works with both.

 

[katekoz]

It just switched it back to String, and it does seem to work with String now. Strange. Thank you very much for your help!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.