Excel VBA Cell format - Increment number format

[oO P2K Oo]

Hi All,

Firstly, thank you for your time helping me with my query.

I used Bill Jelen's "Next invoice number" VBA code to help set me up with an invoice template that will generate a new blank invoice and add 1 to the invoice number. My issue is that I have set the first invoice to "SI-00001", but when I use the code below the invoice changes the next invoice number to "SI-2". I would love it if someone could help me have the next invoice number ("SI-00002") and following invoice numbers to keep the 5 digit format, especially for for the tenth/ hundredth/ thousandth invoice number ("SI-00010"/ "SI-00100"/ "SI-01000").

The VBA code I use:

Sub SaveInvoiceBothWaysAndClear()
Dim NewFN As Variant
' Create the PDF First
NewFN = "E:\OneDrive\Invoices\Sales\PDF\Invoice " & Left(Range("K14").Value, 3) & Mid(Range("K14").Value, 4, 5) & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
' Next, Save the Excel File
ActiveSheet.Copy
NewFN = "E:\OneDrive\Invoices\Sales\Excel\Invoice " & Left(Range("K14").Value, 3) & Mid(Range("K14").Value, 4, 5) & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
' Increment the invoice number
Range("K14").Value = Left(Range("K14").Value, 3) & 1 + Mid(Range("K14").Value, 4, 5)
' Clear out the invoice fields
Range("A21:B38").ClearContents
End Sub

Many thanks in advance.

 

[AlphaFrog]

[COLOR=#333333]Range("K14").Value = Left(Range("K14").Value, 3) & [B]Format([/B]1 + Mid(Range("K14").Value, 4, 5)[B], "00000")[/B][/COLOR]

 

[oO P2K Oo]

Thanks for that, I put it after the "' Increment the invoice number" code.

 

[oO P2K Oo]

I cannot see the Solved button. Otherwise this has been solved.