excel - 下标超出范围，将一个工作簿中的范围复制到另一个工作簿

Question

我想将工作簿中的一系列数据复制到事件工作簿中。代码看起来很简单，但是当我运行代码时出现下标超出范围错误。我究竟做错了什么？
源 = "C:\Data\Demographics.xlsx"
目的地 = "C:\Data\Demographic_Import_2022_04_21.xlsm"

Workbooks(Source).Worksheets("Demographics").Range("B10:j39").Copy _
Workbooks(Destination).Worksheets("Sheet1").Range("B10")

Answer 1

打开工作簿

您需要使用 Workbooks.Open能够使用完整的路径。无论文件是否打开，这都将起作用。

只有当工作簿已经打开时，您才能使用 Workbooks("Demographics.xlsx")和 Workbooks("Demographic_Import_2022_04_21.xlsm")没有路径。

也可以考虑使用 ThisWorkbook对于包含此代码的工作簿(无需指定其名称)。

Sub OpenWorkbooks()
    
    ' Using constants and variables
    
    ' Source
    Const sPath As String = "C:\Data\Demographics.xlsx"
    Const sName As String = "Demographics"
    Const sRangeAddress As String = "B10:J39"
    ' Destination
    Const dPath As String = "C:\Data\Demographic_Import_2022_04_21.xlsm"
    Const dName As String = "Sheet1"
    Const dFirstCellAddress As String = "B10"
 
    ' Source
    Dim swb As Workbook: Set swb = Workbooks.Open(sPath)
    Dim sws As Worksheet: Set sws = swb.Worksheets(sName)
    Dim srg As Range: Set srg = sws.Range(sRangeAddress)
    
    ' Destination
    Dim dwb As Workbook: Set dwb = Workbooks.Open(dPath)
    Dim dws As Worksheet: Set dws = dwb.Worksheets(dName)
    Dim dfCell As Range: Set dfCell = sws.Range(dFirstCellAddress)

    ' Copy.
    srg.Copy dfCell
 
    ' or using constants, no variables (not recommended):
    'Workbooks.Open(sPath).Worksheets(sName).Range(sRangeAddress).Copy _
        Workbooks.Open(dPath).Worksheets(dName).Range(dFirstCellAddress)

    ' or using no constants, no variables (not recommended):
    'Workbooks.Open("C:\Data\Demographics.xlsx").Worksheets("Demographics").Range("B10:J39").Copy _
        Workbooks.Open("C:\Data\Demographic_Import_2022_04_21.xlsm").Worksheets("Sheet1").Range("B10")

End Sub