提问人：ADAN ALEJANDRO RAMIREZ QUINTER 提问时间：11/8/2023 最后编辑：braXADAN ALEJANDRO RAMIREZ QUINTER 更新时间：11/10/2023 访问量：77
下标超出范围 用于从一张工作表复制到另一张工作表的宏中的运行时错误“9”
Subscript out of range Run time error'9' in macro used to copy from one sheet to another
问：
我是编写宏的新手，只是了解它是如何工作的。我需要将数据从一个工作簿复制到另一个工作簿上，这是我想出的宏：
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：下标超出范围，我检查了两个工作簿上的单元格，它们确实一一对一对
我确保所有名字拼写正确，并检查了工作表上的单元格
答：
0赞
CDP1802
11/10/2023
#1
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
评论
"C:\Users\...\...\Monthly Report.xlsx"