Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto updating a pivot table (2003)

    Hi all,
    I have a pivot table that is on a seperate worksheet from the table it gets its data from. As the source data will be changing a lot, I am wondering if there is a way to automatically update the pivot table whenever the source data changes?
    The worksheet the table gets its data from is called "All MMR Radios" and the range where the data is located is A1 to P5987. The pivot table is in a worksheet called "Totals Table" in the same workbook/spreadsheet.
    Is there a way to update the pivot table when the source data changes?
    Thanks heaps.

    Bill

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto updating a pivot table (2003)

    You could add some code to the worksheet change event of the data sheet. The code goes into the "All MMR Radios" sheet in VBA.

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A1:P5987")) Is Nothing Then
    Worksheets("Totals Table").PivotTables(1).PivotCache.Refresh
    End If
    End Sub</pre>


    Steve

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto updating a pivot table (2003)

    Steve,
    That worked perfectly. Thanks so much for your help.

    Bill

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto updating a pivot table (2003)

    You asked to update the pivot on changes to the datasheet, but another approach would be to only update the pivot table whenever the sheet with the pivot table becomes active:

    <pre>Private Sub Worksheet_Activate()
    Me.PivotTables(1).PivotCache.Refresh
    End Sub</pre>


    This code would go into the "Totals Table" sheet. It could make the data entry less sluggish since it would not be updated as frequently.

    Steve

Posting Permissions

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