Excel VBA工作表间数据复制粘贴代码运行报错求助

你遇到的是运行时错误'9'：下标越界，大概率是这几个原因：

• 要复制的源工作簿（New Data.xlsx）或者目标工作簿（Repots.xlsm）没打开
• 文件名/工作表名拼写错了——比如你代码里的Repots.xlsm，是不是少打了个字母？应该是Reports.xlsm吧？
• 引用的工作表（Export/Data）不存在

给你两个修正后的代码方案：

方案1：先检查文件是否打开再复制 #

Sub CopyPasteWorksheet()
    Dim srcWB As Workbook, destWB As Workbook
    Dim srcWS As Worksheet, destWS As Worksheet
    
    ' 检查源工作簿是否打开
    On Error Resume Next
    Set srcWB = Workbooks("New Data.xlsx")
    On Error GoTo 0
    If srcWB Is Nothing Then
        MsgBox "先打开New Data.xlsx再运行代码", vbExclamation
        Exit Sub
    End If
    
    ' 检查目标工作簿（这里修正了拼写错误，改成Reports.xlsm）
    On Error Resume Next
    Set destWB = Workbooks("Reports.xlsm")
    On Error GoTo 0
    If destWB Is Nothing Then
        MsgBox "先打开Reports.xlsm再运行代码", vbExclamation
        Exit Sub
    End If
    
    ' 检查工作表是否存在
    On Error Resume Next
    Set srcWS = srcWB.Worksheets("Export")
    Set destWS = destWB.Worksheets("Data")
    On Error GoTo 0
    If srcWS Is Nothing Or destWS Is Nothing Then
        MsgBox "指定的工作表找不到", vbExclamation
        Exit Sub
    End If
    
    ' 执行复制粘贴
    srcWS.Range("A2:D9").Copy destWS.Range("A2")
End Sub

方案2：让代码自动打开文件 #

如果想不用手动打开文件，直接让代码帮你打开并复制，用这个版本：

Sub CopyPasteWithAutoOpen()
    Dim srcPath As String, destPath As String
    Dim srcWB As Workbook, destWB As Workbook
    Dim srcWS As Worksheet, destWS As Worksheet
    
    ' 替换成你实际的文件路径
    srcPath = "C:\你的文件夹路径\New Data.xlsx"
    destPath = "C:\你的文件夹路径\Reports.xlsm"
    
    ' 打开源工作簿
    Set srcWB = Workbooks.Open(srcPath)
    ' 打开目标工作簿（如果已经打开就直接引用）
    On Error Resume Next
    Set destWB = Workbooks("Reports.xlsm")
    If destWB Is Nothing Then
        Set destWB = Workbooks.Open(destPath)
    End If
    On Error GoTo 0
    
    ' 复制数据
    Set srcWS = srcWB.Worksheets("Export")
    Set destWS = destWB.Worksheets("Data")
    srcWS.Range("A2:D9").Copy destWS.Range("A2")
    
    ' 要是需要的话，这里可以加保存和关闭源文件的代码
    ' srcWB.Save
    ' srcWB.Close
End Sub

最后再提醒下：优先检查文件名拼写，很多时候都是打错字母导致的报错。

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