Set serial number base on date and starting [Solved/Closed]

Report
-
 sam -
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


2 replies

Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
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
Hi Trowa,
Thanks a million for your support. I did as you said. but it didn't work out. I'm not expert in this. Do i have to type SWM/SB/12/07/125 in A1 Cell or only SWM/SB/ because I need your VB extract month and day from system. Please let me know.
Thanks a lot again for ur help
Sam
Dear Trow,

Thanks it's working properly.
Dear Trowa,

I need Year and Month automatically pick in system. pls help.
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
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
Hi,
Thanks a lot but it doesn't work
="SWM/SB/"&YEAR(TODAY())-2000&"/"&IF(MONTH(TODAY())<10;"0"&MONTH(TODAY());MONTH(TODAY()))&"/125"
excel shows error highlights near "0"
Pls help
Regards
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi Sam,

Could it be because I forgot to replace ; with ,?

Regards
Dear TrowaD,

It's working. thanks a lot. you have being a great help for me. have a nice day to you.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!