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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,366
Messages
5,528,269
Members
409,813
Latest member
robyrux

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top