Select Pivot Table Field Error

David_Excel

New Member
Joined
May 7, 2013
Messages
10
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

New Member
Joined
May 7, 2013
Messages
10
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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).

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,837
Messages
5,598,378
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top