Edit invoice data [VBA]
In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing values with new values.
If invoice number already exists, a message box asks if you want to overwrite old values with new values.
VBA code:
Sub Button2_Press with left mouse button on() Dim rng As Range Dim i As Long Dim a As Long Dim rng_dest As Range Application.ScreenUpdating = False 'Check if invoice # is found on sheet "Invoice data" i = 1 Do Until Sheets("Invoice data").Range("A" & i).Value = "" If Sheets("Invoice data").Range("A" & i).Value = Sheets("Invoice").Range("D13").Value Then 'Ask overwrite invoice #? If MsgBox("Overwrite invoice data?", vbYesNo) = vbNo Then Exit Sub Else Exit Do End If End If i = i + 1 Loop i = 1 Set rng_dest = Sheets("Invoice data").Range("D:G") 'Delete rows if invoice # is found Do Until Sheets("Invoice data").Range("A" & i).Value = "" If Sheets("Invoice data").Range("A" & i).Value = Sheets("Invoice").Range("D13").Value Then Sheets("Invoice data").Range("A" & i).EntireRow.Delete i = 1 End If i = i + 1 Loop ' Find first empty row in columns D:G on sheet Invoice data Do Until WorksheetFunction.CountA(rng_dest.Rows(i)) = 0 i = i + 1 Loop 'Copy range B16:I38 on sheet Invoice Set rng = Sheets("Invoice").Range("B16:E38") ' Copy rows containing values to sheet Invoice data For a = 1 To rng.Rows.Count If WorksheetFunction.CountA(rng.Rows(a)) <> 0 Then rng_dest.Rows(i).Value = rng.Rows(a).Value 'Copy Invoice number Sheets("Invoice data").Range("A" & i).Value = Sheets("Invoice").Range("D13").Value 'Copy Date Sheets("Invoice data").Range("B" & i).Value = Sheets("Invoice").Range("F3").Value 'Copy Company name Sheets("Invoice data").Range("C" & i).Value = Sheets("Invoice").Range("B8").Value i = i + 1 End If Next a Application.ScreenUpdating = True End Sub
How to add the macro to your workbook
- Press Alt-F11 to open visual basic editor
- Press with left mouse button on Module on the Insert menu
- Copy and paste above vba code
- Exit visual basic editor
The macro displayed in the picture above is not used in this article.
Invoice category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
This post demonstrates how to: Insert a button to your worksheet Assign a macro to the button Create a basic […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Excel categories
4 Responses to “Edit invoice data [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Why in this example data on B20 is not copied?
Just asking, like this vba will send a sample, highlighting data that needs to be extracted, very similar but not as much detail. (In Spanish).
Thank you. Very Helpful.
Fili,
I understand, the picture is wrong. Cell B20 is also copied. Try the attached file!
Oscar, after my comment I did tried it, absolutely correct, it does copy all the information.
I was looking for something like this, this is very helpful for what I am doing, so Thank you very much.
Have and Excelent Day.
Dear Sir, actually i want to maintain inventory stock by (receiving material as per date wise and company wise , material wise purchase entry),(create invoice with automated invoice numbering and date wise),(printing of invoice),(company wise,date wise,invoice number wise purchase and transaction statement throughout a financial year) and after that (stock of materials with value as per company wise)after the end of year with editing facilities in every where by using VBA Code.
Thanking You
Yours faithfully
akshar