refreshing pivots

Hi,

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.

Thanks.


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

Hi

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()
Me.PivotTables(1).PivotCache.Refresh
End Sub