macro to automatically refresh pivottable

[wally32]

hi

i can see that there are several threads with macro that automatically refresh a pivottable. i have found this macro below

Private Sub Worksheet_Activate()
'If this worksheet is activated, refresh the pivot table
Sheets("sheet4").PivotTables("PivotTable1").RefreshTable

End Sub

my problem is that the pivottable sheet (sheet4 in this example above) would be sheet5 one month, sheet 6 the next and so on. how would the macro above be amended to accomodate this.

thanks

 

[james_lankford]

put in the workbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    on error resume next
    Sh.PivotTables("PivotTable1").RefreshTable
    
End Sub

I think the "on error" will take of any sheets that don't have a pivot table

 

[wally32]

doesn't seem to work. im afraid. nothing seems to change.

 

[james_lankford]

put this in the ThisWorkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    On Error Resume Next
    Sh.Range("Z1").Value = "here"
    
End Sub

just so you can be sure its being called

click on the different tabs and the value in cell Z1 should be filled in

 

[wally32]

sorry, dont know what i done but it does work.
i was thinking about trying to do it so as a new line is entered onto the worksheet it would automatically update the pivottable, think this would be to complicated.

many thanks for your help