Excell Pivot tables

  • Thread starter Thread starter Stan R
  • Start date Start date
S

Stan R

I am having the following problem.

We created pivot table templates that we are refreshing
with data on monthly basis.

The data is coming from database table. The "Category
Grouping" column in the pivot table for soem reason
preserves "old data" even after I make the refresh.
(I did verify that query begind pivot table pulls right
data for that column. I also verified that there was no
manyal substitution for this field)

I can see that data in the "data" section changes (with
latest changes) but column section contains the old data.

Is there a way for me to clear that field/make it refresh
with other data without preserving old values? The only
solution I found so far was to remove this field from
query, refresh it, then add it back, and refresh it again.

Thanks a lot for your help

Stan
 
As posted in response to the same question in excel.misc --

To eliminate the old items from the dropdowns, in Excel 2002, you can
set the MissingItemsLimit property:

'==========================
Sub DeleteMissingItems2002()
'prevents unused items in XL 2002 PivotTable
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables.Item(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

End Sub
'=============================

For earlier versions, you can run the following macro:
'======================
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
Dim i As Integer

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.VisibleFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
End Sub
'================================
 
If the manual field removal is working well, you could record the steps
as you do that, then run the macro the next time the lists need to be
updated. For example, if the field name is "Rep", and it's the second
row field:

Sub FieldRefresh()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Rep")

pf.Orientation = xlHidden
pt.RefreshTable
With pf
.Orientation = xlRowField
.Position = 2
End With
End Sub
 
Back
Top