Set serial number base on date and startingSolved/Closed

Question

Hello, 

I have a local purchase order (LPO) sheet in excel for two different group. I use LPO serial as 
SWM/SB/12/07/125 
Swm/SB is permanent term
12/07 is the current year and month
125 is the current LPO next One would be 126

What i really want is one i print this form serial number need to change as 
SWM/SB/12/07/126 (125 change 126 after print)

Please help ..urgent


Configuration: Windows XP / Firefox 13.0.1

TrowaD · Answer

Hi Sam,

Assuming your LPO serial is in cell A1, otherwise change the reference in the code. Implement the following code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim FP As String
Dim SP As Integer

Application.EnableEvents = False
Cancel = True
     
ActiveWindow.SelectedSheets.PrintOut Copies:=1
     
FP = Left(Range("A1"), 13)
SP = Right(Range("A1"), 3)
Range("A1").Value = FP & SP + 1
     
Application.EnableEvents = True
End Sub
Code needs to be pasted under ThisWorkbook.
Now give the print order and see if the result is the right one.

Best regards,
Trowa

TrowaD · Answer

Hi Sam,

To get the year and month automatically try this formula:
="SWM/SB/"&YEAR(TODAY())-2000&"/"&IF(MONTH(TODAY())<10;"0"&MONTH(TODAY());MONTH(TODAY()))&"/125"

Just to be clear, this formula can be placed in any cell. Just make sure the cell reference in the code (3x A1) is the same as the cell you chose to put the formula in.

Best regards,
Trowa

Set serial number base on date and starting

2 responses

Similar discussions

Subscribe To Our Newsletter!