Filtering two Pivot Tables from a cell referencing a drop down result.

coops365

New Member
Joined
Jul 30, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I'm sure my title makes this sound more complicated than it is.

The facts are these...
  • I have a sheet with two pivot tables that reference different data sources.
  • I have a drop down that allows the user to select a country, this drives a vlookup that generates the two letter ISO code in cell G4.
  • I have a command button that I would like to then change the filter for both pivots to the code listed in G4
this is my (borrowed) code...

VBA Code:
Private Sub CommandButton1_Click()


Dim a As String
Dim pt As PivotTable
 
ThisWorkbook.Worksheets("Look-Up").Activate
a = Worksheets("Look-Up").Cells(4, 7).Value

For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("ISO Code")
.ClearAllFilters
.CurrentPage = a

End With

Next

End Sub

It works once, then throws up a 'Run-time error '1004': Application-defined or object-defined error'

The beginning and end of my VBA experience consists of copy and pasting other peoples' code, so I've no idea where to start. Can anyone advise? Thanks in advance.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Watch MrExcel Video

Forum statistics

Threads
1,114,520
Messages
5,548,528
Members
410,845
Latest member
OldSwimmer1650
Top