Generate Invoice Number Based on date

Good Day All,

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

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

My invoice number will be at "C5"

So I change it manually. My VBA already auto +1 when I save the invoice and is will save as the customer name "C9"

Anyone able to guide me on this please.

Thank You.

Below are my code for my invoice

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

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

To automate C5:

=YEAR(TODAY())&TEXT(MONTH(TODAY()),"00")&TEXT(DAY(TODAY()),"00")&"-001"

Hi AliGW,

I didnt know it so easy just using that formula. Thank you for your time and teaching.

However, the formula did not stay when i save it.

It isn't. That's just a supplement to post #4.

This will not work on its own for serial numbers because the number generated by TODAY() would change each day. It's only for cell C5 referenced by the VBA code.

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are still new here I will provide it for you this time: https://www.excelguru.ca/forums/show...-Based-on-date.)