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.