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")
CodePudding user response:
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")
andWorkbooks("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