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 […]
This post demonstrates how to view saved invoices based on the invoice number using a userform. The userform appears when the […]
Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]
Question: I have a long table The key is actually col B&C BUT…sometime there are few rows with same key […]
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 […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
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