PDA

View Full Version : Pivot Table Filter Button - VBA HELP



joejs
03-28-2019, 08:25 AM
I am working on VBA code in Excel to filter a pivot table (on a different sheet with a different data source) based on the selection on a different pivot table using a button.

I have gone on multiple forums and altered code to my needs but I have been unsuccessful in connecting the code to my needs. I have tried code such as objects and modules for changing pivot table filters and refreshing. I am currently working on building my code from scratch but I have hit a road block. Anything helps! Thanks

I expect to be able to select "Style" in one pivot table and then have that selection populate the other pivot table once I click on the button. Right now there is no error but the fields are not updating. I was wondering if anyone knows how to filter a pivot table on a different sheet with different source data based on another pivot table using VBA?


Option Explicit


Sub Button1_Click()

Dim ws As Worksheet, ws2 As Worksheet
Dim pvtTable As PivotTable, pvtTable2 As PivotTable
Dim pvtField As PivotField, pvtField2 As PivotField
Dim pvtItem As PivotItem, pvtItem2 As PivotItem
Dim s$, a$

'ws.pvtTable.PvtField
Set ws = ActiveSheet
Set pvtTable = ws.PivotTables("PivotTable1")
Set pvtField = pvtTable.PivotFields("Style")

'ws2.pvtTable2.pvtField2
Set ws2 = Sheets(2)
Set pvtTable2 = ws2.PivotTables("PivotTable2")
Set pvtField2 = pvtTable2.PivotFields("Style")

'Setting the filters - issues here?
'Let s = ws.PivotTables("PivotTable1").pvtField
Let s = pvtField
'Let a = ws2.PivotTables("PivotTable2").pvtField2
Let a = pvtField2
Let a = s

End Sub