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")
- You need to use
Workbooks.Opento 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("Demographic_Import_2022_04_21.xlsm")without the paths.
- Also consider using
ThisWorkbookfor 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
Answered By – VBasic2008
Answer Checked By – Candace Johnson (AngularFixing Volunteer)