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

Solved
Report
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
Hello,
please tell the vba code for increase invoice number to 103,leaving others same on opening worksheet


System Configuration: Windows / Firefox 89.0

12 replies

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
Hi TrowaD
Thank you very much for your immediate reply & help to my problem. As, i am not at all having knowledge about vba coding,can you please provide me complete code.
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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

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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Murphy,

Have a look at code line 10:
For x = 3 To qAmount + 2

The 3 stands for the first row you want to start creating invoice numbers.
The 2 stands for the row your initial invoice number is located.

When you change the 'initial invoice number' cell from B2 to B12, then that code line should look like this:
For x = 13 To qAmount + 12


When you enter 5 as the number of invoice numbers you want to create, the creation starts at row 13 and ends at 17 (5+12).

Best regards,
Trowa
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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Murthy,

Then it is working as intended and I'm not sure as what you to see different.

I thought this is what you want:
  • Main Invoice workbook has "TE/2021-22/102" in B12.
  • Upon opening main Invoice workbook, the Invoice number is increased by 1, so it says "TE/2021-22/103" in B12.
  • System "Save as" window opens to save a copy to a location of your choosing.
  • Opening the copy will not increase the invoice number, as it is saved without code.
  • Upon reopening main Invoice workbook, the Invoice number is increased by 1, so now it says "TE/2021-22/104" in B12.
  • System "Save as" window opens to save a copy to a location of your choosing.
  • Etc..


If you don't want the Invoice number to keep increasing in the main Invoice workbook, then you can remove code line 8 from the latest code (ThisWorkbook.Save). This will create duplicate Invoice numbers, which can't be what you are after, right?

Best regards,
Trowa
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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Murthy,

Then indeed I misunderstood you. Now that I do understand you, I'm puzzled as what the issue could be. To be clear, the "Save as" window only appears once for me.

When you manually trigger the "Save as" window, does it also appear twice?
Could you post your workbook (always be careful with sensitive data) to a free filesharing site and then post back the download link? That way I can see if it happens to me to.

Best regards,
Trowa
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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Murthy,

Thanks for the link, but unfortunately, even with your file, I'm not getting a second "Save as" window.

I did some google searches and it appears that others have experienced the same. For some the issue was that they saved to a synced google drive location. Others had to change a save setting in the Excel options.

Since I can't reproduce the issue, I have no idea what might work for you.

It seems I won't be able to help you any further.

Best regards,
Trowa
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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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