Subscript out of range with copy a range in one workbook to another workbook

Question

I have a range of data in a workbook that I would like to copy to the active workbook. The code looks simple, but I am getting a subscript out of range error when I run the code. What am I doing wrong?

Source = "C:\Data\Demographics.xlsx" Destination = "C:\Data\Demographic_Import_2022_04_21.xlsm"

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

Answer 1

Open Workbooks

• You need to use Workbooks.Open to be able to use the complete path. This will work whether the files are open or not.
• Only when the workbooks are already open, you can use Workbooks("Demographics.xlsx") and Workbooks("Demographic_Import_2022_04_21.xlsm") without the paths.
• Also consider using ThisWorkbook for the workbook containing this code (no need to specify its name).

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