updating pivot tables

[jgibbings]

Hi,

I have large spreadsheet with about 10 or so pivot tables from attached
data.
I run new data and replace the attached data with it every week. When
I update the pivots using the wizard it updates but increases the size
of the worksheet regardless of the size of the data. By the end of the
year the worksheet is over 100 megabytes which is much larger than the
dataset. I'm using Excel 2002. It seems that it is keeping the
original data reference. How do I deal with this without creating new
pivot tables every week?

Thanks
Jim

 

[Guest]

Not sure if this is your issue, but a couple of months ago I found the subs
below by Debbie Dalgleish. She wrote about data staying even after it is no
longer 'there'. This may be your issue.
Below are Debbie's solutions to this depending on the version/
-from www.contextures.com/xlpivot04.html - Debbie Dalgleish

'/=================================================/
Public Sub Pivot_Clear_Deleted_Data()
'get rid of items in PivotTable data that are no longer
' in the actual data but still shows in the dropdowns
' - affects all pivot tables on active worksheet
' For data prior to Excel 2002
Dim i As Double, z As Double
Dim ptPivotField As PivotField
Dim ptPivotItem As PivotItem

i = ActiveSheet.PivotTables.Count
If i > 0 Then
With ActiveSheet
For z = 1 To i
With .PivotTables(z)
.RefreshTable
For Each ptPivotField In .VisibleFields
If UCase(ptPivotField.name) <> "DATA" Then
For Each ptPivotItem In ptPivotField.PivotItems
If ptPivotItem.RecordCount = 0 Then
If ptPivotItem.IsCalculated = False Then
ptPivotItem.Delete
End If
End If
Next ptPivotItem
End If
Next ptPivotField
End With
Next z
End With
End If

End Sub
'/=================================================/
'from www.contextures.com/xlpivot04.html - Debbie Dalgleish
'2005/02/23
'In Excel 2002, and later versions, you can
'programmatically change the pivot table properties,
'to prevent missing items from appearing,
'or clear items that have appeared.
'
Sub DeleteMissingItems2002All()
'clears 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
Next pt
Next ws

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

HTH,

 

[jgibbings]

Thanks for the quick reply. I tried the code and it helped but not
enough. I have a data spreadsheet off of which the pivots are based
all in the same workbook. I rerun a MS-Sql query each week and paste
the data over the previous weeks, then i update the pivot rows through
the wizard. Every time i do this the worksheet gets larger so i think
it is keeping the previous weeks data in its cache. The columns are
the same week to week so it is only the value of the cells and the
number of rows that change week to week.

 

[Guest]

I'm not sure if this will help, but when you do your cut and paste, if you
did a Paste Special and selected the Values check box only, would that help?

Just a thought....

-Sharon