I got this code (from somewhere, maybe here) that returns the items selected on a pivot filter.
Function ShowReportFilterItems(rng As Range)
Application.Volatile (True)
myfield = rng.Value
a = rng.PivotTable.Name
With rng.PivotTable.PivotFields(myfield)
N = .PivotItems.Count
For i = 1 To N
If .PivotItems(i).Visible Then
s = s & " " & .PivotItems(i).Name & "."
End If
Next i
End With
ShowReportFilterItems = s
End Function
UDF works fine, it recalculates when I change the filter and when I refresh the pivot BUT I also have a macro initiated by a button that refreshes the pivot (pivot is somewhat hidden, that is why I need the button). The macro is simply:
Sub refresh_rank_pivot()
ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
End Sub
SO, when I refresh the pivot via the button i get a #Value! error. If I reculc (full or F2 enter on one of the cells containing the UDF) it works fine again.
Can anyone please explain, resolve?
Bookmarks