Find match within a column in a different worksheet, then copy paste specific cells to matched row in VBA

Question

I've got two workbooks, one named "LocalBooks" and another named "CentralIndex". All of the entries in the sheet "LocalBooks" have a unique reference number. I'm trying to write something that looks for a row in "CentralIndex" with that matching reference number and then updates specific columns in that row. (I do understand that a match and update entire row is a commonly asked question, but I couldn't find it for just updating specific columns in the row)

Workbook: "Localbooks" - Please assume first cell address is A1, sheet name is books

Workbook: "CentralIndex" - Please assume first cell address is A1, sheet name is Central Index

If my code runs correctly I'd like the "Central Index" to look like this:

With rows 2 (C2,E2,I2), 6 (C6,E6,I6) and 10 updated.

Considerations/Constraints

The above are sample sheets for my task, as I'm unable to share the actual data, but the actual sets are looking over 200+ rows.
There won't be any duplicate reference numbers in the "Central Index" sheet. So multiple matches aren't an issue.
I did contemplate using an array, but got stuck on holding the multiple column values from "Books" and then putting them in the different columns. If there is a way to do that then I welcome it.
I cannot use a classic index/match or other formula solution as the ask is to "Make update with press button" and I cannot amend the "central index" sheet.
In a very ideal world, I'd love for the code to also highlight any rows in "Local Books" that were not matched in the "Central Index". But as my code is very not working I hadn't got that far.

My code below uses the match function to find the row address, however when I go to run it, nothing seems to happen....

Sub Update()

    Dim wbLocal As Workbook
    Dim wbCentral As Workbook
    Dim wsBooks As Worksheet
    Dim wsCentral As Worksheet
    Dim lrBooks As Long
    Dim lrCentral As Long
    Dim i As Long
    Dim rc As Variant
    
    Set wbLocal = Workbooks("LocalBooks.xlsx")
    Set wbCentral = Workbooks("CentralIndex.xlsx")
    Set wsBooks = wbLocal.Worksheets("Books")
    Set wsCentral = wbCentral.Worksheets("Central Index")

    lrBooks = wsBooks.Cells(wsBooks.Rows.Count, 1).End(xlUp).Row
    lrCentral = wsCentral.Cells(wsCentral.Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lrCentral
        rc = Application.Match(wsCentral.Cells(i, 1).Value, wsBooks.Range("A1:A" & lrBooks), 0)
        If Not IsError(rc) Then
            wsBooks.Range("D").Select
            Selection.Copy
            Windows("CentralIndex.xlsx").Activate
            wsCentral.Range("C").Select
            ActiveSheet.Paste
            Windows("LocalBooks.xlsx").Activate
        End If
    Next

End Sub

Debugging doesn't seem to pick anything up, so I haven't even been able to see if the copy paste part works either. (I'm aware that the current iteration of the copy paste won't get me the results above, I just wanted to see if what I'd done worked before using it for the other cells).

Happy to provide more info, and a giant thanks in advance. Promise I am learning so much from each question I ask :)

You are looking for a match from Column A with `wsCentral.Cells(i, 1)` but aren't the ref no in column B on that sheet ? — CDP1802, Oct 24 '21 at 14:06

CDP1802 · Accepted Answer · 2021-10-24T15:43:00.030

Using a Dictionary Object with reference numbers as keys and corresponding Index sheet row numbers as values.

Option Explicit

Sub Update()

    Dim wbLocal As Workbook, wbCentral As Workbook
    Dim wsBooks As Worksheet, wsCentral As Worksheet
    Dim lrBooks As Long, lrCentral As Long
    Dim i As Long, r As Long, rc As Variant
    Dim n As Long, m As Long

    Dim dict As Object, key As String
    Set dict = CreateObject("Scripting.Dictionary")
    
    Set wbLocal = Workbooks("LocalBooks.xlsx")
    Set wbCentral = Workbooks("CentralIndex.xlsx")
    Set wsBooks = wbLocal.Worksheets("Books")
    Set wsCentral = wbCentral.Worksheets("Central Index")

    ' build lookup
    With wsCentral
        lrCentral = .Cells(.Rows.Count, "B").End(xlUp).Row
        For i = 2 To lrCentral
            key = Trim(.Cells(i, "B"))
            If dict.exists(key) Then
                MsgBox "Duplicate Ref No '" & key & "'", vbCritical, "Row " & i
                Exit Sub
            ElseIf Len(key) > 0 Then
                dict.Add key, i
            End If
        Next
    End With

    ' scan books, match ref numbers and update index
    With wsBooks
        lrBooks = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To lrBooks
            key = Trim(.Cells(i, "A"))
            If dict.exists(key) Then
                r = dict(key)
                wsCentral.Cells(r, "C") = .Cells(i, "D") ' Status
                wsCentral.Cells(r, "E") = .Cells(i, "E") ' Date last loaned
                wsCentral.Cells(r, "I") = .Cells(i, "H") ' Currently loaned to
                n = n + 1
            Else
                .Rows(i).Interior.Color = RGB(255, 255, 0)
                m = m + 1
            End If
        Next
    End With
    MsgBox n & " records updated" & vbLf & m & " rows not found", vbInformation
End Sub

just wrote a reference article about this approach, if you want more explanations: https://stackoverflow.com/questions/69698390/how-to-do-a-match-in-vba/69698391#69698391 — ceci, Oct 24 '21 at 16:04

score 0 · Answer 2 · answered Oct 24 '21 at 14:18

If you want to do this in vba you should use a "dictionary". e.g. Runtime '6' Overflow Error - Refactoring Code for Stock Analysis

But based on your description I would recommend to use "Powerquery". In your "CentralIndex" workbook:

Go to menu data > Get data > from file > From workbook => choose "Localbooks.xlsx" and select the sheet to load
Click on "transform data"
On the top left you see "close and load" button, make sure to click on the litle triangle and choose "close and load to", check: connection only.
Go to your sheet in "CentralIndex", click in cell A1
Go to menu data > click on "From table range" and check "my table has header"

If all went well, you are back in powerquery and you have 2 queries (click on the left if you don't see them). To match:

Select 1 of them, click on the column you want to match
In menu "home" > "merge queries" => select the second table and column you want to match, leave the joinKind to left and hit OK.
You should see a new col with a dubbel arrow, click on the arrow and select the columns you want to add.
click on menu "close and load", this time choose the first option and load to new sheet

Let me know how it goes or if you get stuck.

Sadly the person I’m doing this for wants it automated. Also for my own understanding, would this solution work if there is a mismatch with the table headers? I’m not all too familiar with the power query function so apols if its a silly question :) — PeepDeep, Oct 24 '21 at 15:35
yes col headers don't matter, in the "merge" step you'll choose the cols to match. concerning automated, once you set it up in powerquery it's automated, you'll just need to hit the data > refresh button each time the source is updated. you should really give it a try. — ceci, Oct 24 '21 at 16:02

Find match within a column in a different worksheet, then copy paste specific cells to matched row in VBA

2 Answers2