Sequentially create invoice numbers based on existing column data

[jmcarthur86]

Hi There.

I'm building a invoicing system for a friend and have been having some issues finding information on sequential numbering, most places relate to creating a whole new workbook, however i have built a workflow within the sheet itself, and need to keep creating new books, Hopefully my description is succinct enough for someone to help or point me in direction of instructions better suited.

Worksheet 1: "Sales" (Cell C1)
Worksheet 2: "Invoice" (Cell H2)
Worksheet 3: "Invoice History" (A:A)

Button : "Generate Invoice Number" - Located in "Sales"

Desired output: When the button is pressed in "Sales" the vba macro checks the last cell in column A in "Invoice History", it then rounds up by +1 and posts the new invoice number into cell C1 in "Sales", and cell H2 in "Invoice" a separate program will then add the new invoice number as part of its process.

An additional Problem, The invoice contains letters and numbers so if possible, it needs to somehow take the string then + 1 to the number eg: Invoice_01, Invoice_02, and continue rounding up.

Any assistance/thoughts is always appreciated

 

[kweaver]

Welcome to Mr. Excel.

Is this what you want?

Sub InvNo()
Dim lr As Long, newinv As String, i As Long
lr = Sheets("Invoice History").Cells(Rows.Count, "A").End(xlUp).Row
i = 1 + Right(Sheets("Invoice History").Cells(lr, 1), Len(Sheets("Invoice History").Cells(lr, 1)) - WorksheetFunction.Find("_", Sheets("Invoice History").Range("A" & lr)))
Sheets("Invoice History").Cells(lr + 1, 1) = "Invoice_" & i
Sheets("Sales").Cells(1, 3) = "Invoice_" & i
Sheets("Invoice").Cells(2, 8) = "Invoice_" & i
End Sub

 

[jmcarthur86]

Perection, thank you very much for this - was exactly as required!