+ Reply to Thread
Results 1 to 4 of 4

updating pivot tables

  1. #1
    jgibbings
    Guest

    updating pivot tables

    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


  2. #2
    Gary Brown
    Guest

    RE: updating pivot tables

    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,
    --
    Gary Brown
    gary.DeleteThis2SendMeAnEmail.Brown@kinneson.com


    "jgibbings" wrote:

    > 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
    >
    >


  3. #3
    jgibbings
    Guest

    Re: updating pivot tables

    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.


  4. #4
    Sharon
    Guest

    Re: updating pivot tables

    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

    "jgibbings" wrote:

    > 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.
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1