Filtering Pivot Table based on cell values (VBA)

mtdeilus

New Member
Joined
Aug 26, 2014
Messages
11
Currently I'm working on a project where I have a "master" sheet, which has holds information about vendors which is linked to a database in Access. The datasheet and pivot table are refreshed from a connection to the database. What I'm looking to do is have either the value of a cell filter the Pivot table on the second tab. Below is the code that I currently have for this, but while I'm not getting an error it does not work.

"Cand Submitter Org Name" is the field name in the pivot table which im trying to filter
"Pivot" is the name of the sheet of the pivot table.G
G1 is the cell I'm looking to filter.

Also I'm not sure if this is a possibility, but I have a column (B), which has the same values as the pivot table rows. Is it possible to create a link by clicking on a value in the column to automatically filter the pivot table based on a matching value?

I really appreciate the help.



Private Sub Worksheet_Change2(ByVal Target As Range)
If Not Target.Address = Range("G1").Address Then Exit Sub
Dim PT As PivotTable
Dim ptItem As PivotItem
On Error Resume Next
For Each PT In Worksheets("Pivot").PivotTables
With PT.PivotFields("Cand Submitter Org Name")
If .EnableMultiplePageItems = True Then
.ClearAllFilters
End If
Set ptItem = .PivotItems(Target.Value)
If Not ptItem Is Nothing Then
.CurrentPage = Target.Value
End If
End With
Next
End Sub
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Below is the code that I currently have for this, but while I'm not getting an error it does not work.

The code isn't triggering at all because you've modified the name of the procedure from the standard events available to the Worksheet.

The declaration should read...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Note that you can't have more than one Worksheet_Change procedure in the same Worksheet. If you have a second procedure, you'll need to merge the code into one procedure.
Before doing that, remove your other procedure then develop this Worksheet_Change code until it works the way you want.
 

mtdeilus

New Member
Joined
Aug 26, 2014
Messages
11
I guess that's where the original issue came up. I have two tabs in the workbook. One with the raw information "Query Sample", and the second tab is the "Pivot Table". I have some VBA code set up for the Query Sample where there are search boxes, and it auto hides the rows if there are not exact matches. That code is below.

Private Sub Worksheet_Change1(ByVal Target As Range)


Dim c As Range
Dim d As Range

cells.EntireRow.Hidden = False

For Each c In Range("C3:C200")
If c.Value = B1 Then
c.EntireRow.Hidden = True
Else: End If
Next c

For Each d In Range("J3:J200")
If d.Value = E1 Then
d.EntireRow.Hidden = True
Else: End If
Next d
End Sub

It's in this tab where I'm looking for the user enters in cell G1, which will control the pivot table filter. Do you think it's a better option to combine the original code in this post with this? I have the above VBA code above in sheet 1 for the Query sample. Should I put the code in sheet 2 for the pivot table or a module?

Thank you for your help.
 

mtdeilus

New Member
Joined
Aug 26, 2014
Messages
11
I believe what is tripping me up from examples I've found is that my Pivot is coming from an OLAP or Access database. Any idea what this would change code wise for the filter off of cell value macro?
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
The syntax would be different for an OLAP data source. An Access database source wouldn't necessarily be different.

Did you understand two key points in my first reply in post #2:

1. You can't modify the declaration of a Worksheet event from that shown in post #2. (your post #3 shows "Worksheet_Change1" which will fail).

2. Develop this Worksheet_Change code until it works the way you want before trying to merge it with other code.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,579
Members
430,557
Latest member
MK15

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