DH Templates

Auto consecutive numbers in every new worksheet in EXCEL?

I have organized invoices from each month in a workbook. So, there's a work book for October, November, December, etc.. As I create a new workbook for each month, I have a default template that's loads up each time I create a new sheet for that workbook. My question is, each sheet has an invoice number. For example, the first sale of the month will be invoice # 23. I want it so that everytime I open up a new sheet, it adds one to that.. so the next would be invoice #24 and so on. What is the formula for this?

Public Comments

1. Assuming you want the invoice number in Cell A1 of the new worksheet, place the following code in ThisWorkbook in your VBA Project Private Sub Workbook_NewSheet(ByVal Sh As Object) Sh.Cells(1, 1) = 23 + ThisWorkbook.Sheets.Count End Sub OR Public Sheet1 As String Private Sub Workbook_NewSheet(ByVal Sh As Object) If ThisWorkbook.Sheets.Count > 1 Then Sh.Name = ThisWorkbook.Sheets(Sheet1) .Cells(1, 1) + _ ThisWorkbook.Sheets.Count - 1 Sh.Cells(1, 1) = Sh.Name End If End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Row = 1 And Target.Column = 1 Then If ThisWorkbook.Sheets.Count = 1 Then Sh.Name = Sh.Cells(1, 1).Text Sheet1 = Sh.Name Else Sh.Cells(1, 1) = Sh.Name End If End If End Sub (One line has a space that needs to be removed - it was put there to get it to display completely in Answers) Where the new workbook was loaded with one worksheet and the starting invoice number is entered in Cells A1 before any other worksheets are created. With this second method each worksheet is given a name equal to its invoice number. And once there are two or more worksheets the invoice numbers and worksheet names can't be changed. One thing to be a little careful about, if you try to modify this code, is that Sheets(1) always refers to the leftmost worksheet no mater when it was created and all other sheets are also numbered in display order, so changing the order of the sheets can change which sheet is Sheets(1) and will change other sheets. :)