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 did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,113,780
Messages
5,544,190
Members
410,597
Latest member
Jasen79
Top