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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Watch MrExcel Video

Forum statistics

Threads
1,118,754
Messages
5,574,040
Members
412,565
Latest member
roberttaekim
Top