Creating an invoice number based on todays date IN EXCEL

dawood_ee1

New member
Joined
Jan 24, 2020
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2016
Hi,

I am trying to figure out the formula for invoice creation based on todays date with numeric values , for ex. today date 24/1/2020 so my invoice number has to be like 241201,second invoice number to be 241202 incase the the third invoice on next day means it shall be 251203. anyone who can generate formula in excel.
 
You should set an example for your Workbook.

Try
Copy this VBA codes below to your Workbook and 'Save As' *.xls or *.xlsm file
In ThisWorkbook module
Code:
Private Sub Workbook_Open()
Call SetTodayDate
  Application.CutCopyMode = True 'clear clipboard (prevents asking when wb is closed)
End Sub

In standard Module1
Code:
Sub InvoiceNumber()
'Optimize Macro Speed - Disable
Application.ScreenUpdating = False
Application.EnableEvents = False

    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C[9]+1"
    Range("K1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("K1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[1]C[-9]"
    Range("B2").Select

'Optimize Macro Speed - Enable
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.CutCopyMode = True 'clear clipboard (prevents asking when wb is closed)
End Sub
In standard Module2
Code:
Sub SetTodayDate()
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("A2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""dmyy"")&ROW(RC[-10])"
    'Range("K2").Select
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C[9]*1"
    Range("B3").Select
    Application.CutCopyMode = True 'clear clipboard (prevents asking when wb is closed)
End Sub
You can put the last two VBA codes into one Module

When you open Workbook Excel it automatically sets the TODAY Date in the A2 cell to Sheet1.
Clicking on the VBA button automatically increases the Invoice number.

I believe that VBA experts can more elegantly solve the problem by shortening the code.
 

Attachments

  • dawood_ee1-navic-10452.xlsm
    21.5 KB · Views: 28
Did it help?
 
Back
Top