Auto increase invoice number in this format "TE/2021-22/102" Solved/Closed

Question

Hello, 
please tell the vba code for increase invoice number to 103,leaving others same on opening worksheet


System Configuration: Windows / Firefox 89.0

TrowaD · Answer

Hi Murthy,

Use Right(your_value,3), then add 1 to it and then use the combination of Len and Left to chop of the 3 number and add the value you added 1 to.

Can you make that work?

Best regards,
Trowa

TrowaD · Answer

Hi Murthy,

Sure, that's what I normally do, but I was a bit short on time yesterday.

How do you see it happen/work?

The way I see it now, is that you have your invoice numbers in column B. The mentioned invoice number is in B2. The code below will ask you how many invoice numbers need to be created.

The code:
Sub RunMe()
Dim qAmount, rPart, x, y As Integer
Dim lPart As String

qAmount = InputBox("How many invoice numbers would you like to create?")

lPart = Left(Range("B2").Value, Len(Range("B2").Value) - 3)
rPart = Right(Range("B2").Value, 3)

For x = 3 To qAmount + 2
    y = y + 1
    Range("B" & x).Value = lPart & rPart + y
Next x
End Sub

Let us know what you like to see different.

Best regards,
Trowa

murthy · Answer

Hi TrowaD
Thank you very much for the code.My invoicen number is in B12 cell,i have changed the cell b2 to b12. i get yellow hight with right arrow mark before Sub RunMe(). hope you can guide me through this problem.

Sub RunMe()
Dim qAmount, rPart, x, y As Integer
Dim lPart As String

qAmount = InputBox("How many invoice numbers would you like to create?")

lPart = Left(Range("B12").Value, Len(Range("B12").Value) - 3)
rPart = Right(Range("B12").Value, 3)

For x = 3 To qAmount + 2
    y = y + 1
    Range("B" & x).Value = lPart & rPart + y
Next x
End Sub

murthy · Answer

Hi Trowa
Thank you for your valuable time for explaining how it works but, i want my invoice number (in cell B12 & Row is 12) auto increment every time i open excel format for making new invoice & leaving the prefix "TE/2021-22/" same & open save as dialogue box to save an excel copy.

Thanks in advance
Murthy

TrowaD · Answer

Hi Murthy,

For that we use the Workbook_Open event. Place the code below under ThisWorkbook (instead of a standard module):
Private Sub Workbook_Open()
Dim lPart, mPath, mFileName As String
Dim rPart As Integer

mPath = ThisWorkbook.Path
lPart = Left(Range("B12").Value, Len(Range("B12").Value) - 3)
rPart = Right(Range("B12").Value, 3)

Range("B12").Value = lPart & rPart + 1

mFileName = InputBox("Save file as:")
If mFileName = vbNullString Then Exit Sub

ThisWorkbook.SaveAs Filename:=mPath & "\" & mFileName & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

If for any reason you don't want to save your workbook, then either click Cancel or confirm an empty value on the inputbox.

Best regards,
Trowa

murthy · Answer

Hi Trowa
The above code perfectly works. 
But, there are two issues
1.when i save the file,it saves on desktop instead, i want system's default "save as" dialogue box to appear so that, i can browse the destination for the file to save.
2.The file is saved on desktop,when i open the saved copy,the invoice number changing which should not happen because, that saved file is for reference & invoice number should be unchanged. It should saved as a new workbook.

Best regards,
Murthy

TrowaD · Answer

Hi Murthy,

Ok, then this should do it: 
Private Sub Workbook_Open()
Dim lPart As String
Dim rPart As Integer

lPart = Left(Range("B12").Value, Len(Range("B12").Value) - 3)
rPart = Right(Range("B12").Value, 3)
Range("B12").Value = lPart & rPart + 1
ThisWorkbook.Save

Application.Dialogs(xlDialogSaveAs).Show "DefaultName", xlOpenXMLWorkbook
End Sub

When you open your default file, the invoice number is increased by 1. The default file is saved, so it remembers the increase. Then the default Save As window is opened, to save a copy of the default file.

When you look at code line 10, you will see "DefaultName". This will be the initial file name in the Save As window. You can change this as you wish or if you don't want an initial name, delete the text, but leave the quotation marks.

The last part of code line 10, xlOpenXMLWorkbook, will set the extention of the copy to .xlsx (or workbook without codes). This is needed to remove the code from the copy so it doesn't increase the invoice number again. Since the workbook does contain a code, you will be asked if you are sure to save the workbook without codes. Click "Yes".

Best regards,
Trowa

murthy · Answer

Hello Trowa
Thank you. Code explained above is entered in ThisWorkbook in vba project. It is working by increasing 1 number & opening system dialog box to save a copy but, after saving as macro-free workbook again main invoice workbook increasing 1 number & opens up save dialog box, which means, it opens up again after saving a copy.

Regards,
Murthy

murthy · Answer

Hello Trowa
You have well understood my problem.Everything is working fine,Upon opening main Invoice workbook, the Invoice number is increased by . But, System "Save as" window opens   twice even after, I save a copy,System "Save as" window opens again to save a copy to a location. I have tried removing Line 8 "ThisWorkbook.Save". But still "Save As" window opens up for second time to save a copy to my desired location.i think i am clear to you with my problem now.

Regards,
Murthy

murthy · Answer

Hi Trowa
When I manually trigger the "Save as" window, does it appears once.
But, with this code,it appears twice.
Please find the attached file link.

https://www.mediafire.com/file/q2tux6c3ftyno17/TE+INVOICE+FORMAT.xlsm/file

Regards,
Murthy

murthy · Answer

Hi Trowa
Thank you for your valuable time & patience for solving my issues.

As you rightly said, it was problem with Excel Options Menu.

I have disabled all Add-ins. Now, it's working perfectly.

Invoice number increases & "Save As" window opens only once.

But, I want "Invoice format" workbook to be open. 
Now, it is closing after "Save As" window opens & after saving workbook.
so that, if i want to make any changes in invoice details, i can modify details in main invoice format.

Thank you
Murthy

TrowaD · Answer

Hi Murthy,

Good to hear you figured out the issue.

To leave the main Invoice workbook open, we have to create a workaround. Because to create a copy and leaving the main Invoice workbook open, we can't use the "Save as" window. So we have to re-open the main Invoice workbook without triggering the Invoice number increase and creating another save.

To bypass this I added a question at the start, asking you to increase the Invoice number and create a copy. First click Yes and the second time click No.

Here is the code:
Private Sub Workbook_Open()
Dim lPart, mFile As String
Dim rPart, qRun As Integer

qRun = MsgBox("Do you want to increase invoice number and create copy?", vbYesNo)
If qRun = vbNo Then Exit Sub

lPart = Left(Range("B12").Value, Len(Range("B12").Value) - 3)
rPart = Right(Range("B12").Value, 3)
Range("B12").Value = lPart & rPart + 1
ThisWorkbook.Save
mFile = ThisWorkbook.FullName

Application.Dialogs(xlDialogSaveAs).Show "DefaultName", xlOpenXMLWorkbook
Workbooks.Open (mFile)
End Sub

You can also consider creating a button to run a code, but then it obviously doesn't autorun.

Best regards,
Trowa

Auto increase invoice number in this format "TE/2021-22/102"

12 responses

Subscribe To Our Newsletter!