March 22, 2022

Worksheet Change Event Macro to Manipulate Data in Excel

How to use a worksheet change event macro in Excel to manipulate invoices data. Watch the video below:

Worksheet change event macro to manipulate data in Excel

A user receives payments for invoices in parts. For example, his total invoice value may be $100,000 due on 27th March 2022. The payment is made in parts like $50000 on 3rd March 2022, $30000 on 14th March and $20000 on 26th March. Now the user wants to remove the invoice entry from the worksheet as soon as the total invoice amount is received. He also wishes to copy the details of invoice and the part payments in another worksheet as a backup. How can the complete process be automated? We can achieve the goal of automation using a macro involving a worksheet change event. The worksheet change event will be triggered only when the total payments made is equal to the total invoice amount due. The complete macro VBA code code is given below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, lastrow1 As Long, lastrow2 As Long

lastrow1 = Application.WorksheetFunction.CountA(Sheet1.Range(“A:A”))
lastrow2 = Application.WorksheetFunction.CountA(Sheet2.Range(“A:A”))

Sheet1.Activate

For i = 2 To lastrow1

If Cells(i, 2) > 0 And Cells(i, 7) > 0 And Cells(i, 2) = Cells(i, 7) Then
    Range(Cells(i, 1), Cells(i, 7)).Copy Destination:=Sheets("sheet2").Cells(lastrow2 + 1, 1)
    Sheet1.Cells(i, "A").EntireRow.Delete
End If

Next i
End Sub