Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
clearing pivot table field values
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
clearing pivot table field values
"graham" wrote: 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: '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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Missing values from a pivot table field | Excel Worksheet Functions | |||
Pivot table not recognizing same field values | Excel Discussion (Misc queries) | |||
Pivot Table Field Values not sorted | Excel Discussion (Misc queries) | |||
Selection of multiple values for pivot table field | Excel Discussion (Misc queries) | |||
Fill a Listbox with Values from a Pivot Table Field - an Example | Excel Programming |