This post demonstrates a macro that automatically selects cell A1 on each sheet right before you close a workbook. The VBA code also moves the view so cell A1 is the upper left cell on all sheets.
This macro is different from regular macros, it is executed when something happens, Microsoft calls this an Event. An event macro has a designated name and must be placed in the sheet module or worksheet module. If your event code is not working you probably saved the code in a regular module.
You can see a list of available events in the workbook or sheet module.
The following code selects cell A1 in all visible sheets right before you close the workbook. You can save this macro in a regular module as well, however, you then need to run the macro manually.
'This Event macro fires before you close a workbook and before the user is prompted to save changes.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Declare variables and data types
Dim sht As Worksheet, csheet As Worksheet
'Don't show any changes the macro does on the screen, this will also make the macro faster.
Application.ScreenUpdating = False
'Assigns object active sheet to variable csheet so we can go back to this sheet when the macro is finished.
Set csheet = ActiveSheet
'Iterate through each worksheet in active workbook
For Each sht In ActiveWorkbook.Worksheets
'Check if worksheet is not hidden
If sht.Visible Then
'Select cell A1 in active worksheet
'Zoom to first cell
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
'Contine with remaining worksheets
'Go back to the worksheet when this event started
'Show all changes made to the workbook
Application.ScreenUpdating = True
Where to copy the code
Press Alt-F11 to open Visual Basic Editor
Double click ThisWorkbook in Project Explorer.
Ctrl + R opens Project Explorer.