VBA Excel: Assigning range values to a new range
Solution 1
you've gotta qualify the Cells calls with a worksheet object too:
ThisWorkbook.Sheets(1).Range(ThisWorkbook.Sheets(1).Cells(1, 1), ThisWorkbook.Sheets(1).Cells(1, 3)).Value = _
BookA.Sheets(1).Range(BookA.Sheets(1).Cells(1, 1), BookA.Sheets(1).Cells(1, 3)).Value
for contiguous ranges like that you can also use Resize:
ThisWorkbook.Sheets(1).Cells(1, 1).Resize(, 3).Value = _
BookA.Sheets(1).Cells(1, 1).Resize(, 3).Value
Solution 2
I think ultimately you are going about this in not the best way to solve the problem. You have object.object.object.object
notation in your code, which is cumbersome and hard to interpret and fix.
If you define some more variables, the code will be easier to troubleshoot and solve your problem:
Dim myAddr as String 'A string to represent the Address of the range
myAddr = Cells(1,1).Address & ":" & Cells(1,3).Address
ThisWorkbook.Sheets(1).Range(myAddr).Value = BookA.Sheets(1).Range(myAddr).Value
teepee
Updated on December 18, 2020Comments
-
teepee over 3 years
I am having trouble assigning values from one workbook range to a range in my current workbook. When I assign my range using Range("A1:C1") this code works fine, however when my range is defined using Range(Cells(1,1),Cells(1,3)) the function fails:
Sub CopyRange() Dim inputExcel As Excel.Application, BookA As Workbook Path_A = ThisWorkbook.Path & "\Book_A.xlsx" Set inputExcel = New Excel.Application Set BookA = inputExcel.Workbooks.Open(Path_A, ReadOnly:=True) 'THIS WORKS: ThisWorkbook.Sheets(1).Range("A1:C1").Value = _ BookA.Sheets(1).Range("A1:C1").Value 'THIS DOESN'T WORK: ThisWorkbook.Sheets(1).Range(Cells(1, 1), Cells(1, 3)).Value = _ BookA.Sheets(1).Range(Cells(1, 1), Cells(1, 3)).Value End Sub
I know this must be a simple syntax issue but I haven't been able to figure it out. How can I get the Range assignments using "Cells" to work?