Results 1 to 2 of 2

Thread: refreshing pivots

  1. #1

    refreshing pivots

    Register for a FREE account, and/
    or Log in to avoid these ads!


    I am trying to get my pivot table to refresh automatically when new data has been entered and for the pivot to refresh automatically with the latest dated information as per my example.

    I am completely stumped and have included the code below, please help.


    Private Sub Worksheet_Activate()
    Dim ptItem As PivotItem, sCurrent As String

    sCurrent = UCase(Format(Date, "DATE"))

    Application.ScreenUpdating = False

    With ActiveSheet.PivotTables("PivotTable2").PivotFields("DATE")
    .PivotItems(sCurrent).Visible = True

    For Each ptItem In .PivotItems
    If UCase(ptItem) <> sCurrent Then ptItem.Visible = False
    Next ptItem

    End With

    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  2. #2
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Located near Abergavenny, South Wales, UK

    Your data is not in the first normal form that is required for Pivoting.
    In the attached file, I have unpivoted your data into the correct format, and then created a Pivot Table from the new data table.

    With your data in the correct format, and with the PT report on a separate sheet, the Activate code on the report shet needs to be simply

    Private Sub Worksheet_Activate()
    End Sub
    Attached Files Attached Files

    Roger Govier
    Microsoft Excel MVP

Posting Permissions

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