Select Pivot Table Field Error

[David_Excel]

Hi,

I 've been trying to change a pivot table filter depending on the value in cell a1, and whilst there are numerous examples on how to do this, I seem to keep getting errors where others do not. Here is the simple code that I'm using:

Sub PivotsUpdate()

Sheets("Herd_Structure").Select

Worksheets("Herd_Structure").PivotTables("PivotTable9").PivotFields("Location").ClearAllFilters
Worksheets("Herd_Structure").PivotTables("PivotTable9").PivotFields("Location").CurrentPage = Range("a1")

End Sub

Clearing all filters works fine (selects all options, but apparently this is normal), but it won't then select the item in cell a1 from the list of locations. Cell a1 is a text value if that makes any difference.

I get a runtime error 1004, application/object defined error on the penultimate line, where I try to select range a1 as my location.

Can anyone offer any assistance please?

Thanks, David

 

[David_Excel]

Anyone have any suggestions?

 

[Jerry Sullivan]

Hi David,

Two things to check first are:
1. Confirm that the Location field is in the Report Filters area of the PivotTable. The code would be different if that field is in the Row Labels or Column Labels area of the PivotTable.

2. Check that the spelling of the value in Cell A1 is an exact match for a PivotItem. A leading or trailing space could cause a mismatch.

 

[David_Excel]

Okay thanks, I'll have a look at this soon.

 

[David_Excel]

Okay thanks, I'll have a look at this soon.

I can get it to change items using

.PivotItems(Station).Visible = True rather than CurrentPage = station, hopefully this is still okay. My issue now is in clearing the previously selected filters.

I cant use Visible = False because I don't know what is currently selected, so I've tried using .clearallfilters, but all this seems to do is select everything, even though it's placed before the code to select the station.

I notice that when I record a macro to change the station it uses the line CurrentPage = "(All)". Is this important? My current code looks as follows:

Dim Station As String

Station = Worksheets("Herd_Structure").Range("Station")

With Worksheets("Herd_Structure").PivotTables("PivotTable9").PivotFields("Station")
.ClearAllFilters ' seems to select all stations
.CurrentPage = "(All)" ' unsure what this does
.PivotItems(Station).Visible = True ' selects correct station when clearallfilters isn't included in the code
End With


Thanks once again

 

[Jerry Sullivan]

Is the field "Station" in the Report Filters area of the PivotTable report?
You can check this by looking at the PivotTable Field List pane.

Your description of the recorded macro implies that this field is in the Report Filters area; however if that's correct then this code should work (provided the value assigned to the variable Station matches a PivotItem in that field).

Sub Test()
 Dim Station As String

 Station = Worksheets("Herd_Structure").Range("Station")

 With Worksheets("Herd_Structure").PivotTables("PivotTable9") _
      .PivotFields("Station")
   .ClearAllFilters 
   .CurrentPage = Station
 End With

End Sub