excel 下标超出范围宏中的运行时错误“9”用于从一个工作表复制到另一个工作表

30byixjq 于 11个月前发布在其他

关注(0)|答案(1)|浏览(120)

我是一个全新的编写宏，刚刚开始了解它是如何工作的。我需要将数据从一个工作簿复制到另一个工作簿，这是我想出的宏：

Option Explicit
Sub CopyMainTable()
    Dim SourceWorkbook As Workbook
    Dim DestWorkbook As Workbook
    
    ' Set references to the source and destination workbooks
    Set SourceWorkbook = Workbooks("Monthly Report.xlsx")
    Set DestWorkbook = Workbooks("ANNUAL REPORT.xlsx")
    
    ' Copy data from source to destination
    SourceWorkbook.Sheets("SITE1").Range("B19:Av43").Copy Destination:=DestWorkbook.Sheets("SITE1").Range("B19")
    
    Set SourceWorkbook = Nothing
    Set DestWorkbook = Nothing

    
End Sub

字符串
问题是，当它运行时，它会出现一个运行时错误9：下标超出范围，我检查了两个工作簿上的单元格，它们确实一一匹配
我确保所有的名字都拼写正确，并检查了电池上的电池。

excel

来源：https://stackoverflow.com/questions/77442048/subscript-out-of-range-run-time-error9-in-macro-used-to-copy-from-one-sheet-to

1条答案

按热度按时间

9q78igpj1#

Option Explicit

Sub CopyMainTable()

    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet
    Dim rngCopy As Range
    Dim sFolder As String, sourceFile As String, s As String
   
    ' Set references to the source and destination workbooks
    Set wbDest = ThisWorkbook
    sFolder = wbDest.Path & "\"
    sourceFile = "Monthly Report.xlsx"
    
    ' check source file exists then open
    s = sFolder & sourceFile
    On Error Resume Next
    Set wbSource = Workbooks.Open(s, ReadOnly:=True)
    If wbSource Is Nothing Then
        MsgBox sourceFile & " not found in " & sFolder, vbCritical
        Exit Sub
    Else
        ' check sheet
        Set wsSource = wbSource.Sheets("SITE1")
        If wsSource Is Nothing Then
            MsgBox "Sheet SITE1 not found in " & wbSource.Name, vbCritical
            wbSource.Close
            Exit Sub
        End If
    End If
    On Error GoTo 0
    
    ' Copy data from source to destination
    Set rngCopy = wsSource.Range("B19:AA43")
    rngCopy.Copy Destination:=wbDest.Sheets("SITE1").Range("B19")
    
    MsgBox rngCopy.Address & " copied from " & wbSource.Name, vbInformation
    wbSource.Close

End Sub

字符串

赞(0）回复(0）举报 11个月前

我来回答

excel 下标超出范围宏中的运行时错误“9”用于从一个工作表复制到另一个工作表

1条答案

相关问题

热门标签

最新问答