Auto select pivot when new items are added

[happy_smiler1]

Hi All,

I have uploaded a sample worksheet trying to automatically update a pivot table, is there a way to update a pivot table by automatically selecting new products when the pivot table is updated rather than go into each pivot table each month and manually selecting new products?.

Thanks for any help in advance.

[ashishmehra2010]

Hi,

Try the attached file. The code is the Sheet1.

Please Login or Register  to view this content.

Regards,
AM

[happy_smiler1]

Hi,

Many thanks for your quick response, is there anyway of adding what you have put above into this piece of code, the code below automatically updates the pivot table according to the month, however I would need to incorporate your code into this code in order to both update the month and the products within the pivot table?

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

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

Application.ScreenUpdating = False

With ActiveSheet.PivotTables("Hospital").PivotFields("Month")
.PivotItems(sCurrent).Visible = True

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

Worksheets("Acc-Pro £").PivotTables("Hospital").RefreshTable

End With

End Sub



Many thanks