如何通过VBA实现跨Excel工作簿自动同步单元格数据（无需打开目标文件）

需求说明 #

修改工作簿「予備品.xlsm」中的任意单元格时，自动将对应数据同步至未打开的工作簿「予備品1.xlsm」的同位置单元格（如A1修改后同步到「予備品1.xlsm」的A1）。

原代码存在的问题 #

• 缺少自动触发机制：原代码需要手动调用HelloCaller，无法实现修改单元格时自动执行
• 工作簿存在性判断逻辑错误：On Error GoTo 0的位置不正确，导致无法正确捕获工作簿未打开的情况
• 未处理多单元格批量修改：原代码仅支持单个单元格同步，批量修改时会失效
• 打开工作簿未做隐藏处理：同步时会弹出目标工作簿窗口，影响操作体验

修正后的完整VBA代码 #

在「予備品.xlsm」中需要同步的工作表模块（比如「付属品1」工作表）中插入以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim secondWorkbookPath As String
    Dim secondWorkbook As Workbook
    Dim secondSheet As Worksheet
    Dim cell As Range
    
    ' 目标工作簿路径
    secondWorkbookPath = "C:\Users\WS035\Desktop\連絡書_MACRO\予備品1.xlsm"
    
    ' 尝试获取已打开的目标工作簿
    On Error Resume Next
    Set secondWorkbook = Workbooks("予備品1.xlsm")
    On Error GoTo 0 ' 恢复默认错误处理
    
    ' 如果目标工作簿未打开，则以隐藏方式打开
    If secondWorkbook Is Nothing Then
        Set secondWorkbook = Workbooks.Open( _
            Filename:=secondWorkbookPath, _
            ReadOnly:=False, _
            Visible:=False ' 隐藏打开，避免弹窗
        )
    End If
    
    ' 指定目标工作表（需与源工作表对应）
    Set secondSheet = secondWorkbook.Sheets("付属品1")
    
    ' 遍历所有修改的单元格，逐个同步
    For Each cell In Target
        secondSheet.Cells(cell.Row, cell.Column).Value = cell.Value
    Next cell
    
    ' 保存并关闭目标工作簿（如果不需要保持打开）
    secondWorkbook.Save
    secondWorkbook.Close SaveChanges:=False
    
    ' 释放对象
    Set secondSheet = Nothing
    Set secondWorkbook = Nothing
End Sub

代码说明 #

• 自动触发：通过Worksheet_Change事件，只要修改当前工作表的单元格，就会自动执行同步逻辑
• 工作簿处理：先检查目标工作簿是否已打开，未打开则隐藏打开，完成同步后自动保存关闭
• 批量支持：通过For Each循环遍历所有修改的单元格，支持批量修改同步
• 错误处理：正确使用On Error Resume Next和On Error GoTo 0，避免异常导致代码中断

注意事项 #

• 确保目标工作簿路径secondWorkbookPath正确无误，路径中的文件夹和文件名不要有变动
• 目标工作表名称"付属品1"需与实际一致，否则会报错
• 启用宏：两个工作簿都需要启用宏才能正常运行，保存时需选择.xlsm格式
• 权限问题：若目标路径存在权限限制，需确保Excel有读写权限

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