Which formula can I use to automatically generate a new invoice number?

[Jeffeverton]

Hi there I would like to know how I can set the following type of invoice numbers to be recorded on a different sheet by the use of a button form control and how I can use another button form control to automatically generate the next invoice number when I press the button as the sequence follows below

FP5435
FP5436
FP5437

I have in sheet 1 the following Cell D5 FP5435, I have inserted 2 button form controls in sheet 1 the first one is to add the invoice number in cell D5 to a record of invoices in sheet 3 , the second button is to start a new invoice number with the sequence continuing. If you could possibly explain what formula/function I can use it would be much appreciated.

Invoice.xlsm
	A	B	C	D	E
3
4
5		Invoice No.		FP5435
6
Sheet1

Invoice.xlsm
	A	B	C	D	E
1	Invoice No.	Company	Description	Amount	Date
2
3
4
Sheet3

 

[mumps]

If I understood correctly, assign the macro below to your first button:

Sub AddInvoiceNumber()
    Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1) = Sheets("Sheet1").Range("D5")
End Sub

and assign the macro below to your second button:

Sub NewInvoiceNumber()
    With Sheets("Sheet1")
        .Range("D5") = Left(.Range("D5"), 2) & Right(.Range("D5"), 4) + 1
    End With
End Sub

 

[Micron]

Why not just have one button that gets the last value in sheet 3, calculates the new value and enters it in the next row of sheet 3?

 

[Jeffeverton]

If I understood correctly, assign the macro below to your first button:

Sub AddInvoiceNumber()
    Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1) = Sheets("Sheet1").Range("D5")
End Sub

and assign the macro below to your second button:

Sub NewInvoiceNumber()
    With Sheets("Sheet1")
        .Range("D5") = Left(.Range("D5"), 2) & Right(.Range("D5"), 4) + 1
    End With
End Sub

The second one works perfectly thank you but the first one I am having a bit of a problem as I am about to show you, don't take note of the cell reference in the original post as I started on a new workbook. The invoice number is now in Cell C3 . As you can see the correct invoice number shows in A2 but the other information recorded goes to the next row.



Sub RecordofInvoice()

Sheets("Record Of Invoices").Cells(Rows.Count, "A").End(xlUp).Offset(1) = Sheets("Invoice Template").Range("C3")

custname = Range("B10")
amt = Range("I41")
dt_issue = Range("C5")
term = Range("C6")

Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)

nextrec = invno
nextrec.Offset(0, 1) = custname
nextrec.Offset(0, 2) = amt
nextrec.Offset(0, 3) = dt_issue
nextrec.Offset(0, 4) = dt_issue + term

End Sub

Invoice Template.xlsm
	B	C	D
3	Invoice No.	FP5435
Invoice Template

Invoice Template.xlsm
	A	B	C	D	E
2	FP5435
3	0	ABC Ltd	£6 120,00	26 02 2024	27 03 2024
Record Of Invoices

 

[Jeffeverton]

Why not just have one button that gets the last value in sheet 3, calculates the new value and enters it in the next row of sheet 3?

hi there could you perhaps show me with a formula/code example

 

[Tupe77]

Do you mean something like this?


Create a unique "Invoice No." value to the Record Of Invoices sheet.
Invoice No. does not appear on the Invoice Template sheet to avoid multiple checks.
(Uniqueness is checked when the Invoice No. is created and transferred directly to the Record Of Invoices sheet.)

I'm not sure about all the variables and their typing as they don't appear in the example you provided.

Sub TS_AddUniqueID()
Dim wsRecord As Worksheet: Set wsRecord = Worksheets("Record Of Invoices")
Dim wsTemp As Worksheet: Set wsTemp = Worksheets("Invoice Template")

Dim LastRow As Long: LastRow = wsRecord.Cells(Rows.Count, "A").End(xlUp).Row
Dim InvNoRNG As Range: Set InvNoRNG = wsRecord.Range("A2:A" & LastRow)
Dim InvNoARR As Variant: InvNoARR = InvNoRNG.Value2
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary"): dict.CompareMode = vbTextCompare
Dim iRow As Long

' ***** Read Invoice No. list and check that there are no duplicates.
For iRow = 1 To UBound(InvNoARR, 1)
    If dict.Exists(InvNoARR(iRow, 1)) Then
        MsgBox "Invoice No. list contains duplicates!" & dict(InvNoARR(iRow, 1)): exit sub
    Else
        dict.Add InvNoARR(iRow, 1), iRow
    End If
Next iRow

' ***** Read Invoice No. list and get previous Invoice No. (max)
For iRow = 1 To UBound(InvNoARR, 1)
    InvNoARR(iRow, 1) = CLng(Mid(InvNoARR(iRow, 1), 3))
Next iRow
Dim PrevInvNo As Long: PrevInvNo = WorksheetFunction.Max(InvNoARR) ' Previous Invoice No.

Dim InvNo As String: InvNo = "FP" & PrevInvNo + 1 ' Create New Invoice No.
    If dict.Exists(InvNo) Then ' Checking Invoice No. uniqueness.
        MsgBox "The new Invoice No. creation failed. It's a duplicate for some unknown reason!" & InvNo: Exit Sub
    End If

' **** Added definition of variables.
Dim custname As String, amt As Currency, dt_issue As Variant, term As Variant
custname = wsTemp.Range("B10")
amt = wsTemp.Range("I41")
dt_issue = wsTemp.Range("C5")
term = wsTemp.Range("C6")

Dim nextrec As Range
Set nextrec = wsRecord.Range("A" & LastRow + 1)
    
nextrec = InvNo
nextrec.Offset(0, 1) = custname
nextrec.Offset(0, 2) = amt
nextrec.Offset(0, 3) = dt_issue
nextrec.Offset(0, 4) = dt_issue + term

End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[mumps]

The invoice number is now in Cell C3 .

The data you posted shows the invoice number in B3. Please clarify. Do the headers in row 1 of the Record of Invoices sheet start in A1?

 

[Micron]

hi there could you perhaps show me with a formula/code example

Assuming your invoice numbers are in column A of sheet 3 and always begin with 2 letters :

Sub WriteNewInvNum()
Dim Lrow As Long
Dim rng As Range

Lrow = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Sheets("Sheet3").Range("A" & Lrow)
rng.Offset(1,0) = Left(rng,2) & Mid(rng,3)+1

set rng = Nothing

End Sub

 

[Jeffeverton]

The data you posted shows the invoice number in B3. Please clarify. Do the headers in row 1 of the Record of Invoices sheet start in A1?

No check again the invoice number FP5435 is in C3. I merged C3 and D3 . B3 has the invoice number text in. Yes all the headers are in row 1 of the Record of invoices sheet starting at A1. The other details does not get recorded in the same row as the invoice number as seen in the Records of invoices sheet.

 

[mumps]

These worked for me:

Sub AddInvoiceNumber()
    Sheets("Record of Invoices").Cells(Rows.Count, "A").End(xlUp).Offset(1) = Sheets("Invoice Template").Range("C3")
End Sub

Sub NewInvoiceNumber()
    With Sheets("Invoice Template")
        .Range("C3") = Left(.Range("C3"), 2) & Right(.Range("C3"), 4) + 1
    End With
End Sub

You should avoid merging cells because they most often create problems for Excel macros.