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_Click()
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
- Click 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.
Related articles
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies [โฆ]
Populate listbox with unique distinct values [VBA]
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 [โฆ]
Invoice template with dependent drop down lists
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on [โฆ]
Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a [โฆ]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies [โฆ]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The [โฆ]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The [โฆ]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible [โฆ]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower [โฆ]
3 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.
English
Spanish
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.