clearing pivot table field values

[Guest]

I have a pivot table that I use for several different departments. The pivot table is linked to an access query that I often simply change the criteria in to switch from one department to the next. When I refresh the pivot table...tada... same pivot table, graphs, summaries, etc but with the new client.

one problem, if you go into the field settings, you see all of the previously refreshed data points. i.e. if I create the pivot table and include employee names, when I refresh the table with a new department, it still shows the old names under the field settings.

any "easy" way of clearing this without completely re creaing the pivot table each time?

 

[Guest]

I have a pivot table that I use for several different departments. The pivot table is linked to an access query that I often simply change the criteria in to switch from one department to the next. When I refresh the pivot table...tada... same pivot table, graphs, summaries, etc but with the new client.

one problem, if you go into the field settings, you see all of the previously refreshed data points. i.e. if I create the pivot table and include employee names, when I refresh the table with a new department, it still shows the old names under the field settings.

any "easy" way of clearing this without completely re creaing the pivot table each time?

Graham,

Try this: it's from Debra Dalgliesh's site www.contextures.com

Public Sub DeleteOldItemsFromPivot()
'From Web: mailto: (e-mail address removed)
'gets rid of unused items in PivotTable
'based on MSKB (202232)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Long
On Error Resume Next
Sheets(P1Sheet).Unprotect Password:="haifa"
With CSRPivot1
For Each pf In .PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
End With
NewCSRPivot.ProtectAndEnable
End Sub

Regards
Ian