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
"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