I have an auto_open macro which auto refreshes a pivot table in workbook A on opening and the pivot refreshes every 1 minute. This works fine but when I open another unrelated Excel workbook B, the auto_open macro in workbook A runs again and re-opens workbook A even when I close down workbook A.
I've noticed that in the VBA editor that the VBA modules for workbook A appear in the VBA editor in workbook B even though I have closed down workbook A.
I've tried using Private sub auto_open in a module thinking it would stay away from other workbooks and workbook_open in the workbook but no luck. Could it be the timer I've added?
My code is tagged.
Private Sub Auto_Open() Application.ScreenUpdating = False Application.DisplayAlerts = False Application.DisplayStatusBar = True Application.StatusBar = "UPDATING ..." 'Pivot table refresh Dim pt As PivotTable For Each pt In ActiveSheet.PivotTables pt.RefreshTable Next pt 'Timer to rerun refresh every 1 minute Application.OnTime Now + TimeValue("00:01:00"), "Auto_Open" Application.StatusBar = "" Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub