Generate Invoice Number Based on date

[Jongnj87]

Good Day All,

I am just curious whether am I able to auto generate invoice number based on actual date.

E.g. Today is 5 Apr 2021 and my Invoice number usually will be 050420210001(DDMMYYY0001)

So I change it manually. My VBA already auto +1 when I save the invoice.
'for PDF Draft file save
Sheet9.Range("A1:I51").ExportAsFixedFormat xlTypePDF, Filename:= _
"C:\Users\JOYCELYN SEE\Desktop\FENG YUN\RECEIPT\Draft PDF\" & Sheet9.Range("C9").Value, Openafterpublish:=True

Anyone able to guide me on this please.

Thank You.

 

[John_w]

auto generate invoice number based on actual date.

Use the Format function with the current date: Format(Date, "DDMMYYYY")

So I change it manually. My VBA already auto +1 when I save the invoice.

Your code doesn't show this auto +1 when it creates the invoice PDF. I assume this is the "0001" part of the invoice number and you have a cell which your VBA code adds 1 to for the next sequential invoice number.

This macro includes all the above points and increments the value in cell A2 for the next sequential invoice number.

Public Sub Create_Invoice_PDF()

    Dim PDFfile As String
   
    With ActiveWorkbook.Sheet9
        PDFfile = "C:\Users\JOYCELYN SEE\Desktop\FENG YUN\RECEIPT\Draft PDF\" & Format(Date, "DDMMYYYY") & Format(.Range("A2").Value, "0000") & ".pdf"
        .Range("A1:I51").ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        'Increment sequential invoice number
        .Range("A2").Value = .Range("A2").Value + 1
        MsgBox "Created " & PDFfile
    End With
   
End Sub

 

[Jongnj87]

Use the Format function with the current date: Format(Date, "DDMMYYYY")

Your code doesn't show this auto +1 when it creates the invoice PDF. I assume this is the "0001" part of the invoice number and you have a cell which your VBA code adds 1 to for the next sequential invoice number.

This macro includes all the above points and increments the value in cell A2 for the next sequential invoice number.

Public Sub Create_Invoice_PDF()

    Dim PDFfile As String
  
    With ActiveWorkbook.Sheet9
        PDFfile = "C:\Users\JOYCELYN SEE\Desktop\FENG YUN\RECEIPT\Draft PDF\" & Format(Date, "DDMMYYYY") & Format(.Range("A2").Value, "0000") & ".pdf"
        .Range("A1:I51").ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        'Increment sequential invoice number
        .Range("A2").Value = .Range("A2").Value + 1
        MsgBox "Created " & PDFfile
    End With
  
End Sub

Sorry i pasted the wrong code.

it should be this
Sheet9.Range("C5").Value = Sheet9.Range("C5").Value + 1

 

[Jongnj87]

Private Sub CommandButton1_Click()
'for Final Words Strikethrough
Range("D47").Font.Color = vbRed
Range("D47").Font.Strikethrough = True

'for PDF Draft file save
Sheet9.Range("A1:I51").ExportAsFixedFormat xlTypePDF, Filename:= _
"C:\Users\JOYCELYN SEE\Desktop\FENG YUN\RECEIPT\Draft PDF\" & Sheet9.Range("C9").Value, Openafterpublish:=True

'for Final Word UnStrikethrough
Range("D47").Font.Color = vbBlack
Range("D47").Font.Strikethrough = False

'for xl file save
ActiveSheet.Copy
With ActiveWorkbook
.SaveAs "C:\Users\JOYCELYN SEE\Desktop\FENG YUN\RECEIPT\Draft Receipt\" & Sheet9.Range("C9").Value, FileFormat:=xlOpenXMLWorkbookMacroEnabled

'for Receipt number
Sheet9.Range("C5").Value = Sheet9.Range("C5").Value + 1

'for clear receipt
Sheet9.Range("C9:I13").ClearContents
Sheet9.Range("B16:B35").ClearContents
Sheet9.Range("F16:F35").ClearContents
Sheet9.Range("G16:G35").ClearContents
End With

End Sub

Private Sub CommandButton3_Click()
'for Draft Word Strikethrough
Range("C47").Font.Color = vbRed
Range("C47").Font.Strikethrough = True

'for Final Word UnStrikethrough
Range("D47").Font.Color = vbBlack
Range("D47").Font.Strikethrough = False

'for PDF Final file save
Sheet9.Range("A1:I51").ExportAsFixedFormat xlTypePDF, Filename:= _
"C:\Users\JOYCELYN SEE\Desktop\FENG YUN\RECEIPT\Fianl Receipt\" & Sheet9.Range("C9").Value, Openafterpublish:=True

'for Draft Word UnStrikethrough
Range("C47").Font.Color = vbBlack
Range("C47").Font.Strikethrough = False

End Sub

 

[John_w]

Sorry i pasted the wrong code.

it should be this
Sheet9.Range("C5").Value = Sheet9.Range("C5").Value + 1

Therefore change the 3 occurrences of A2 in my code to C5.

 

[Jongnj87]

How about the format Date? At where should I put?

 

[John_w]

It's already in my code, and with C5 instead of A2 for the 0001 part of the PDF file name:

PDFfile = "C:\Users\JOYCELYN SEE\Desktop\FENG YUN\RECEIPT\Draft PDF\" & Format(Date, "DDMMYYYY") & Format(.Range("C5").Value, "0000") & ".pdf"

 

[Joe4]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Generate Invoice Number Based on date
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.