drop down arrow missing on pivot table when refreshed

K

kimz

I used this to get rid of old items in a pivot table.

Sub DeleteItems()
'
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.VisibleFields
If pf.Name <> "Data" Then
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
End If
Next
Next
Next
end sub

when refreshing the pivot table the drop down arrows on the page by
fields disappear. We are running excel 2000. Any ideas?



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **
 
Top