VBA跨工作簿匹配列值后向匹配行复制指定单元格数据的问题

• 遍历逻辑错误：原代码遍历总索引表行去匹配本地表，和需求「用本地表数据更新总索引匹配行」的逻辑完全相反
• 单元格引用语法错误：wsBooks.Range("D") 缺少行号参数，属于非法单元格地址，因此复制粘贴逻辑完全无法执行
• 大量使用Select/Activate这类依赖窗口焦点的操作，稳定性极差，且运行效率低
• 仅实现了单列复制逻辑，未覆盖需求要求的C、E、I三列更新，也没有未匹配行高亮的逻辑

Sub UpdateCentralIndex()
    Dim wbLocal As Workbook, wbCentral As Workbook
    Dim wsBooks As Worksheet, wsCentral As Worksheet
    Dim lrBooks As Long, lrCentral As Long, i As Long
    Dim dict As Object, refId As String
    
    ' 校验工作簿是否已打开
    On Error Resume Next
    Set wbLocal = Workbooks("LocalBooks.xlsx")
    Set wbCentral = Workbooks("CentralIndex.xlsx")
    On Error GoTo 0
    If wbLocal Is Nothing Or wbCentral Is Nothing Then
        MsgBox "请确保LocalBooks.xlsx和CentralIndex.xlsx均已打开", vbCritical
        Exit Sub
    End If
    
    ' 绑定工作表
    Set wsBooks = wbLocal.Worksheets("books") ' 注意工作表名大小写，和实际一致即可
    Set wsCentral = wbCentral.Worksheets("Central Index")
    Set dict = CreateObject("Scripting.Dictionary") ' 用字典做索引，查询效率更高
    
    ' 清空本地表原有高亮标记
    wsBooks.Rows("2:" & wsBooks.Rows.Count).Interior.ColorIndex = xlNone
    
    ' 读取总索引表的参考编号与行号映射
    lrCentral = wsCentral.Cells(wsCentral.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lrCentral
        refId = CStr(wsCentral.Cells(i, 1).Value)
        If Not dict.exists(refId) Then
            dict(refId) = i ' 存储参考编号对应的总索引行号
        End If
    Next
    
    ' 遍历本地表更新总索引，同时标记未匹配行
    lrBooks = wsBooks.Cells(wsBooks.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lrBooks
        refId = CStr(wsBooks.Cells(i, 1).Value)
        If dict.exists(refId) Then
            ' 此处按需求调整列对应关系：示例为本地表B列→总索引C列，本地D列→总索引E列，本地H列→总索引I列
            wsCentral.Cells(dict(refId), "C").Value = wsBooks.Cells(i, "B").Value
            wsCentral.Cells(dict(refId), "E").Value = wsBooks.Cells(i, "D").Value
            wsCentral.Cells(dict(refId), "I").Value = wsBooks.Cells(i, "H").Value
        Else
            ' 未匹配行标黄
            wsBooks.Rows(i).Interior.Color = RGB(255, 255, 0)
        End If
    Next
    
    MsgBox "更新完成，共更新" & dict.Count & "条匹配数据", vbInformation
    
    ' 释放对象
    Set dict = Nothing
    Set wsBooks = Nothing
    Set wsCentral = Nothing
    Set wbLocal = Nothing
    Set wbCentral = Nothing
End Sub

• 全程不会向单元格写入任何index/match公式，也不会修改总索引表的原有结构，完全符合需求
• 采用字典做索引，即使后续数据量增长到数千行，运行速度也不会有明显下降
• 请根据实际的列对应关系，修改代码中更新三列数据的列号参数即可
• 未匹配到的本地表行自动填充黄色高亮，方便人工核对

内容的提问来源于stack exchange，提问作者PeepDeep