How to increase a number automatically when workbook is opened?
If you want to increase a number cell by 1 automatically when you open the workbook each time, of course, you can add 1 manually. But, is there any better method to add 1 for the number automatically when opening the Excel document?
The following simple VBA code may help you to add 1 for the number cell automatically when the workbook is opened each time, please do as this:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. In the Project-VBAProject pane, double-click ThisWorkbook to open the Module, then, copy and paste the below code into the module, see screenshot:
VBA code: Increase a number automatically when the workbook is opened:
Private Sub Workbook_Open() Worksheets("Sheet1").Range("A2") = Worksheets("Sheet1").Range("A2") + 1 End Sub
Note: In the above code, Sheet1 and A2 are the sheet and cell which contains the number you want to increase automatically. Please change them to your need.
3. Then you should save the Excel file as Excel Macro-Enabled Workbook format, see screenshot:
From now on, when you open this specific workbook, the number in cell A2 in Sheet1will be added 1 automatically.
Note: This code is only applied to the number cell.
You are guest
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 years agoThank you! Too easy! My simple quote form now allots a new number every time I open it. Previous forum help pages I visited assume too much prior knowledge of Excel and terminology. This was "Macros for Dummies"
To post as a guest, your comment is unpublished.· 1 years agoBoth screenshots are the same. There is no screenshot for #3 "save the Excel file as Excel Macro-Enabled Workbook format" as directed.
To post as a guest, your comment is unpublished.· 1 years agoHi, Leslie,
Thanks for your comment, the second picture has been updated, please chek it.
Thanks a lot!