Use Excel VBA to Match IDs and Copy Data Between Worksheets
Question details
The user needs a VBA routine that finds matching IDs in an Updating worksheet and copies selected source fields into fixed columns in an Update worksheet.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- Data in the Updating sheet must update existing records in the Update sheet by using an ID column as the matching key.
- Observed behavior
- Only the intended destination fields should be replaced; destination columns must remain in their current positions.
Confirm that each ID is unique in the Updating sheet and that both sheets use the same ID format, such as text or numbers. Resolve the mapping conflict before running the macro: source column K and source column F cannot both populate destination column G in the same update.
Use a VBA dictionary to match IDs and update fixed destination columns
Use this approach when you want a clear, workbook-based macro that updates selected fields without moving or inserting any columns.
The macro reads IDs from the Updating sheet into a dictionary, then checks each ID in the Update sheet. Edit the field mappings in the code so every destination column has only one source column.
In the example, column F is the ID column on both worksheets. The mapping array demonstrates how to copy source columns E, G, H, and J into fixed destination columns E, G, H, and J.
In Excel, press Alt+F11, then select Insert > Module to create a standard VBA module.
Paste the code below into the module. Change "Update", "Updating", "F", and the source-to-destination mappings to match your workbook layout: Sub UpdateByID() Dim wsUpdate As Worksheet, wsUpdating As Worksheet Dim idMap As Object, lastUpdate As Long, lastUpdating As Long Dim r As Long, srcRow As Long, item As Variant Dim mappings As Variant Set wsUpdate = ThisWorkbook.Worksheets("Update") Set wsUpdating = ThisWorkbook.Worksheets("Updating") Set idMap = CreateObject("Scripting.Dictionary") 'Each pair is: source column, destination column. 'Do not assign two source columns to the same destination column. mappings = Array(Array("E", "E"), Array("G", "G"), Array("H", "H"), Array("J", "J")) lastUpdating = wsUpdating.Cells(wsUpdating.Rows.Count, "F").End(xlUp).Row For r = 2 To lastUpdating If Len(Trim(wsUpdating.Cells(r, "F").Value)) > 0 Then idMap(CStr(wsUpdating.Cells(r, "F").Value)) = r End If Next r lastUpdate = wsUpdate.Cells(wsUpdate.Rows.Count, "F").End(xlUp).Row For r = 2 To lastUpdate If idMap.Exists(CStr(wsUpdate.Cells(r, "F").Value)) Then srcRow = idMap(CStr(wsUpdate.Cells(r, "F").Value)) For Each item In mappings wsUpdate.Cells(r, item(1)).Value = wsUpdating.Cells(srcRow, item(0)).Value Next item End If Next r End Sub
If destination column G should receive Updating column F, use Array("F", "G"). If it should receive Updating column K, use Array("K", "G"). Remove the other mapping so column G is updated by only one source field.
Return to Excel, press Alt+F8, select UpdateByID, and click Run. The macro updates matching rows only and leaves all destination columns in place.
Run an ADO UPDATE JOIN for a query-based worksheet update
Use this advanced option when the workbook is saved locally and you prefer an SQL-style update between two worksheet ranges.
Match IDs and update worksheet data with WPS Spreadsheet
WPS Spreadsheet provides a familiar worksheet environment for organizing update tables and working with VBA-compatible macro workflows. Use fixed ID columns, define an explicit source-to-destination mapping, and run a macro to update only the required cells without rearranging your sheet.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select WPS Spreadsheet, and open the Excel workbook containing the Update and Updating sheets.
- 2. Verify the ID and field mappings: Check that the ID columns contain consistent values, then decide the single source column that should populate each destination column.
- 3. Open the macro editor: Use the Developer tools or press Alt+F11 where available to open the VBA editor, then choose Insert > Module.
- 4. Add and run the update macro: Paste the ID-matching macro, update the worksheet names and column mappings, then run it from the Macro dialog.
Frequently Asked Questions
How can VBA match IDs without sorting either worksheet?
Load the IDs from the Updating sheet into a Scripting.Dictionary, then look up each ID from the Update sheet. This matches records directly and does not require sorting either list.
Why does my VBA update overwrite the wrong destination column?
Check the source-to-destination mapping. Each destination column must have one explicit source column. If both Updating!F:F and Updating!K:K are assigned to Update!G:G, choose one source or create separate destination columns.
What happens when an ID exists in Update but not in Updating?
A dictionary-based macro can simply skip that row, leaving its existing Update values unchanged. Add an Else block if you want to flag unmatched IDs instead.
Can I use an ADO UPDATE JOIN to update Excel worksheets?
Yes, an ADO UPDATE statement can join worksheet ranges by an ID field and update selected fields. Ensure the workbook path, provider, range references, HDR setting, and relative field numbers are correct.
Why are ADO fields named F1, F2, and F5 instead of worksheet column letters?
With HDR=NO, the ADO provider assigns relative names such as F1 and F2 based on the columns inside the queried range. For a range beginning at column A, F1 is column A; if the range begins elsewhere, the numbering starts at that first range column.
