I have two Pivot Reports and two data sheets in my template workbook. New
data sheets are added by a third party program. When the book is opened,
excel start up macros replace the data sheets with the new data and refresh
the pivots. But sometimes, the drop lists are not in sorted order - even
though the data rows are sorted in the data sheets. How can the droplists be
made to be sorted ?
--
Jeff

Here is some code that refreshes the lists...

Sub DeleteOldItemsWB()
'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

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

Sub DeleteMissingItems2002All()
' prevents unused items in PivotTable
'
' in Excel 2002 and later versions
' If unused items already exist,
' run this macro then refresh the table
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh
Next pt
Next ws

End Sub

Here is a good link to get more info...
http://www.contextures.com/tiptech.html

--
HTH...

Jim Thomlinson


"Jeff" wrote:

I have two Pivot Reports and two data sheets in my template workbook. New
data sheets are added by a third party program. When the book is opened,
excel start up macros replace the data sheets with the new data and refresh
the pivots. But sometimes, the drop lists are not in sorted order - even
though the data rows are sorted in the data sheets. How can the droplists be
made to be sorted ?
--
Jeff

thanks
--
Jeff


"Jim Thomlinson" wrote:

Here is some code that refreshes the lists...

Sub DeleteOldItemsWB()
'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

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

Sub DeleteMissingItems2002All()
' prevents unused items in PivotTable
'
' in Excel 2002 and later versions
' If unused items already exist,
' run this macro then refresh the table
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh
Next pt
Next ws

End Sub

Here is a good link to get more info...
http://www.contextures.com/tiptech.html

--
HTH...

Jim Thomlinson


"Jeff" wrote:

I have two Pivot Reports and two data sheets in my template workbook. New
data sheets are added by a third party program. When the book is opened,
excel start up macros replace the data sheets with the new data and refresh
the pivots. But sometimes, the drop lists are not in sorted order - even
though the data rows are sorted in the data sheets. How can the droplists be
made to be sorted ?
--
Jeff