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 rund 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 press with left mouse button on ThisWorkbook in Project Explorer.
   Ctrl + R opens Project Explorer.
   
3. Copy aboveVBAa code.
4. Paste to worksheet module.
   
5. Exit visual basic editor

Get the Excel file

Reset-selection-to-A1.xlsm

Recommended article

Recommended articles

Customize the ribbon and how to add your macros
Save links to your favorite macros in a personal tab on the ribbon for easy access and become more productive. […]

Macro category

Add or remove a value in a drop down list programmatically
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]

Add values to a regular drop-down list programmatically
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]

Add values to a two-dimensional table based on conditions [VBA]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]

Excel categories

Comments (8)