VBA & Macro Problems

Update or Append Excel Rows by ID with a VBA Macro

Phi Hung Vo Nov 12, 2026 869 views

Question details

Create a VBA macro that compares IDs in column B of Sheet1 and Sheet2, updates matching rows, and appends records with IDs that do not exist on Sheet2.

Update or Append Excel Rows by ID with a VBA Macro

Product: Microsoft Excel VBA
Device & OS: not provided
Scenario: The user needs to synchronize data from Sheet1 to Sheet2 using an ID as the lookup key.
Observed behavior: Matching IDs should have columns A:D updated on Sheet2, while missing IDs should be added to the next available Sheet2 row.

Before you start

Make sure both worksheets use column B for IDs and that row 1 contains headers. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) and enable macros before running the code.

Solution 1Recommended

Use Range.Find to update or append Sheet2 rows by ID

Use this method when Sheet1 is the source list and Sheet2 must be updated with matching records or expanded with new IDs.

The macro searches Sheet2 column B for each ID in Sheet1. When it finds an exact match, it replaces cells A:D in the existing destination row; otherwise, it writes the source row to the first empty row in Sheet2.

Open the Visual Basic Editor

In Excel, press Alt+F11, then select Insert > Module to create a standard VBA module.

Paste the update and append macro

Paste this code into the module: Sub UpdateOrAppendRowsByID() Dim wsSource As Worksheet, wsDest As Worksheet Dim lastSourceRow As Long, lastDestRow As Long Dim sourceRow As Long, destRow As Long Dim recordID As Variant Dim foundCell As Range Set wsSource = ThisWorkbook.Worksheets("Sheet1") Set wsDest = ThisWorkbook.Worksheets("Sheet2") lastSourceRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row If lastSourceRow < 2 Then Exit Sub lastDestRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Row If Application.WorksheetFunction.CountA(wsDest.Rows(1)) = 0 Then lastDestRow = 1 For sourceRow = 2 To lastSourceRow recordID = wsSource.Cells(sourceRow, "B").Value If Len(Trim(CStr(recordID))) > 0 Then Set foundCell = wsDest.Columns("B").Find(What:=recordID, _ After:=wsDest.Cells(1, "B"), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not foundCell Is Nothing Then destRow = foundCell.Row Else lastDestRow = lastDestRow + 1 destRow = lastDestRow End If wsDest.Cells(destRow, "A").Resize(1, 4).Value = _ wsSource.Cells(sourceRow, "A").Resize(1, 4).Value End If Next sourceRow MsgBox "Sheet2 has been updated.", vbInformation End Sub

Confirm sheet names and columns

Replace "Sheet1" and "Sheet2" in the Set wsSource and Set wsDest lines if your worksheet tabs have different names. Keep the ID field in column B, or change both "B" references to the correct ID column.

Run the macro

Press Alt+F8, select UpdateOrAppendRowsByID, and select Run. Review Sheet2 to confirm that existing ID rows were updated and new IDs were appended.

Use Range.Find to update or append Sheet2 rows by ID

Exact ID matching is used: The macro uses LookAt:=xlWhole, so an ID such as 100 will not match 1000. Ensure that IDs are consistently stored as text or numbers on both worksheets.

Solution 2

Use a worksheet table and refresh the ID list before running VBA

Use this option when the source or destination range changes frequently and you want the macro to include newly added rows reliably.

Work with spreadsheet macros

Synchronize ID-Based Rows in WPS Spreadsheet

With WPS Spreadsheet on a desktop version that supports VBA macros, you can open an XLSM workbook, edit the macro in the Visual Basic Editor, and run the ID-based update process to keep records aligned between worksheets.

1. Open the macro-enabled workbook: In WPS Spreadsheet, select Menu > Open and choose the XLSM workbook containing Sheet1 and Sheet2.
2. Enable macro access: When WPS displays a macro security prompt, enable macros only for a workbook you trust.
3. Open the VBA editor: Use the Developer tools or the VBA editor entry point available in your WPS Spreadsheet desktop version, then open the workbook's standard module.
4. Add and run the macro: Paste the update-and-append VBA code, save the workbook in XLSM format, and run UpdateOrAppendRowsByID from the Macro dialog.

Update matching spreadsheet records by the ID in column B.Append source records that do not yet exist in the destination sheet.Supports Microsoft Excel XLSX and macro-enabled XLSM workbook formats.Use a familiar spreadsheet interface for formulas, tables, and worksheet data management.

microsoft office alternative - wps office

Frequently Asked Questions

How does the macro decide whether to update a row or add a new row?

For each Sheet1 ID in column B, the macro uses Range.Find on Sheet2 column B. It writes columns A:D to the found row when there is an exact match; when no match is found, it writes the values to the next available Sheet2 row.

Can I use a different column for the ID value?

Yes. Change each "B" reference in the VBA code, including Cells(..., "B") and Columns("B"), to the letter of the column that contains your unique ID.

Why are some IDs not being matched by Range.Find?

IDs may appear identical but be stored differently, such as text in one sheet and numbers in the other, or they may contain extra spaces. Format both ID columns consistently and remove leading or trailing spaces before running the macro.

What happens if Sheet2 contains duplicate IDs?

Range.Find returns the first matching cell, so only the first duplicate row is updated. Remove duplicates from Sheet2 column B first if each ID should correspond to one record.

Do I need to save the workbook as XLSM?

Yes. Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) to retain VBA code. Saving as XLSX removes macros.

Excel & Data Problems