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 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
- Press Alt-F11 to open Visual Basic Editor
- Double press with left mouse button on ThisWorkbook in Project Explorer.
Ctrl + R opens Project Explorer.
- Copy aboveVBAa code.
- Paste to worksheet module.
- Exit visual basic editor
Recommended article
Recommended articles
Save links to your favorite macros in a personal tab on the ribbon for easy access and become more productive. […]
Macro category
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
Excel categories
8 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?
Oct 2021
Working OK on Office 365.
Thanks
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!