This post will guide you how to write a VBA macro to auto-number a number in a cell when a new workbook is opened. For example, when you open the workbook each time, you want to increase a number in a specific cell B1. And you can try to use an excel VBA macro to quickly achieve the result.
You just need to do the following steps:
#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
#2 then the “Visual Basic Editor” window will appear.
#3 Double-click ThisWorkbook in the left Project_VBAProject pane to open the Module, then copy and paste the following VBA code into the code window.
Private Sub Workbook_Open() Worksheets("Sheet1").Range("B2") = Worksheets("Sheet1").Range("B2") + 1 End Sub
#3 then you need to save the current workbook as ExcelMacro-Enabled Workbook format.
#4 close the current workbook, then reopen it, you will see the B2 value will be increased automatically.
So far, we have setup an auto-incrementing number in cell B2, when the workbook is opened, the value in this cell will increase by 1.