Runtime error 438 in Pivot table macro

Hello
Could someone please advise why I get a runtime 438 error in the following:

Sub PivotStatus()

Dim pt As PivotTable

Set pt = Worksheets("Status TEST").PivotTables("PivotTable1")
pt.PivotFields("Organisation").ClearAllFilters
pt.PivotFields("Organisation").CurrentPage = Range("Pivot_Org")

End Sub

This is excel 2003

thanks

Hi,
There is no ClearAllFilters method in 2003. It was added (along with a lot of the other good pivot functionality) in 2007.

Why do you need it in 2003 if you are setting the CurrentPage anyway?

Sorry, I don't understand the question.

Just to outline what I'm trying to do:

I have a pivot table ("PivotTable1") in the worksheet "Status TEST" which needs to change on the value selected in the "Organisation" filter. This value is found in the cell with range name "Pivot_Org" which is in a different worksheet.

Thanks

What I am asking is why this will not suffice

Please Login or Register  to view this content.

since it will set the page field to whatever the specified value in Pivot_Org is.

'Organisation' is a page field, rather than row or column field? What is the value in the named range?

The value is one of the choices of Organisations so for example in this case "Operations". In that cell (Pivot_Org) is a lookup formula which looks at the user input then pulls in a value equal to one the organisation choices. I thought of putting .Value after Range("Pivot_Org") but that didn't work either. However when I hover the cursor over the Range("Pivot_Org") it is showing the correct Organisation choice.

Could you post a small sample workbook, with the bare minimum of data for the pivot and any confidential information removed/censored?

Organisation is not a page field, so you cannot use CurrentPage on it. You will have to set the pivotitem you want visible, then loop through the pivotitems, making all the others invisible. You need to do it in that order to ensure that you always have one item visible.

Based upon your sample file

Please Login or Register  to view this content.

This is untested but should be more or less right

Please Login or Register  to view this content.

Unfortunately get following Runtime 1004 error message when selecting "All" items, still works fine on individual choices:

"Unable to set the visible property of the PivotItem class"

Sub PivotStatus()

Dim pt As PivotTable
Dim pfld As PivotField
Dim pitm As PivotItem
Dim varFilter

varFilter = Lcase$(Sheets("Data").Range("F13").Value)
Set pt = Worksheets("Status TEST").PivotTables("PivotTable3")
pt.ManualUpdate = True
Set pfld = pt.PivotFields("Organisation")
If varFilter = "all" then
For Each pitm In pfld.PivotItems
pitm.Visible = True <------------------ERROR highlighted here
Next pitm
Else
pfld.PivotItems(varFilter).Visible = True
For Each pitm In pfld.PivotItems
pitm.Visible = (LCase(pitm.Name) = varFilter)
Next pitm
End If
pt.ManualUpdate = False
End Sub

Thanks for helping

Possibly old items in the data, or items that have no data - perhaps

Please Login or Register  to view this content.

Glad you noticed. ;-)