You might at some point want Excel to generate a unique number for your worksheets. Suppose, were creating forms such as statements, invoices, etc., and you want a unique number for each form. And you also want Excel to remember the number from one session to the next.
There are more than one way to look at this. If your numbers do not need to be sequential, you could create a number based on the current time of day, in seconds. The following macro can be added to the ThisWorksheet object:
Private Sub Workbook_NewSheet(ByVal Sh As Object) Dim lTicket As Long lTicket = CLng(Time * 24 * 60 * 60) Sh.Range("A1") = lTicket End Sub
The macro will be triggered every time a new worksheet is added to your workbook. It takes the current time, converts it to an integer number of seconds, and then places that value into cell A1. The likelihood of duplicating numbers within any day is very remote, but it is possible that it could happen over a period of time. (i.e., if you created a ticket at the exact same time today that you did previously).
To work around this problem, you could create a number this way:
Private Sub Workbook_NewSheet(ByVal Sh As Object) Dim sTemp As String sTemp = Format(Date, "yymmdd") & Format(Time, "hhmmss") Sh.Range("A1") = sTemp End Sub
This version of the event handler creates a number based on both the date and time. Unless you are creating your numbers very quickly, this approach should reduce the possibility of duplicate numbers generated by the macro.
If the numbers must be sequential within your current workbook, then you can define a name that contains the current high value of your number, and then a macro that places that number in a cell on a new worksheet and increments the value of the stored number.
Follow the steps below to learn how:
- Display the Formulas tab of your Ribbon.
- In the Defined Names group click on the Define Name tool to display the New Name dialog box.
- In the Name box, enter a name such as MaxNum.
- In the Refers To area at the bottom of the dialog box, enter an equal sign followed by the value you want used for your next number.
- Click on OK. The new name is stored in the workbook.
- Now, add the following macro to the ThisWorksheet object in the VBA Editor:
Private Sub Workbook_NewSheet(ByVal Sh As Object) Dim iMax As Integer iMax = Mid(ThisWorkbook.Names("MaxNum"), 2) Sh.Range("A1") = iMax iMax = iMax + 1 ThisWorkbook.Names("MaxNum").RefersTo = "=" & iMax End Sub
This macro will be executed every time you insert a new worksheet in the workbook. It will retrieve the value you stored in the MaxNum, place that value into cell A1 of the new worksheet, and then increment what is stored in MaxNum.
All credit for the macros in this article goes to Allen Wyatt. I am not nearly so clever as he!