get-digital-help.com

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.

VBA code

'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
    'Activate sheet
    sht.Activate
    'Select cell A1 in active worksheet
    Range("A1").Select
    'Zoom to first cell
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1
  End If
'Contine with remaining worksheets
Next sht

'Go back to the worksheet when this event started 
csheet.Activate
'Show all changes made to the workbook
Application.ScreenUpdating = True

End Sub

Where to copy the code

1. Press Alt-F11 to open Visual Basic Editor
2. Double click ThisWorkbook in Project Explorer.
   Ctrl + R opens Project Explorer.
   
3. Copy aboveVBAa code.
4. Paste to worksheet module.
   
5. Exit visual basic editor

Recommended article

Add your personal Excel Macros to the ribbon

If you find yourself using the same macros over and over again, you can create a personal *.xlsb file that […]

Add your personal Excel Macros to the ribbon