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

katekoz

Board Regular
Joined
Jan 20, 2020
Messages
91
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Try this

VBA Code:
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

Board Regular
Joined
Jan 20, 2020
Messages
91
Office Version
  1. 2016
Platform
  1. Windows
I made the changes. I now get an error of "Application-defined or object-defined error" on the PF.CurrentPage = PTF line.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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

Board Regular
Joined
Jan 20, 2020
Messages
91
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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?
 

katekoz

Board Regular
Joined
Jan 20, 2020
Messages
91
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,476
Messages
5,636,556
Members
416,923
Latest member
jarri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top