Select cell A1 on all sheets before you close a workbook [VBA]
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
- Press Alt-F11 to open Visual Basic Editor
- Double click ThisWorkbook in Project Explorer.
Ctrl + R opens Project Explorer.
- Copy aboveVBAa code.
- Paste to worksheet module.
- Exit visual basic editor
Recommended article
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. […]
Customize the ribbon and how to add your macros
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
7 Responses to “Select cell A1 on all sheets before you close a workbook [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
thanks... works great!
I'm using Excel 2010, but it doesn't work on my computer.
Something changed in Excel?
Thanks a lot, very helpful I took the code further by inserting same data on all selected cell, thanks again saved a lot of time
Hi, For this to save in the A1 position so it opens in this same way do I need to add ActiveWorkbook.Save?
I should note I was trying to do this in the Personal notebook in "ThisWorksheet" so that it would happen for every document I close. Is this possible?
Working great for my requirement... Thank you so much
sharanesh,
you are welcome!