Reputation: 1234
I have this code that opens first workbook, second workbook, copies a range from the first one and pastes it into the second one. I want to make it select the cell right after the pasted range in the second workbook, but it failes with Object required
error.
Sub tes()
'**VARIABLES**
Dim folderPath As String
folderPath = "Y:\plan_graphs\final\mich_alco_test\files\"
Dim fileTitle As String
fileTitle = "5.xlsx"
Dim dataWorkbook As Workbook
Set dataWorkbook = Application.Workbooks.Open(folderPath & fileTitle)
Dim copyRange As Range
Set copyRange = dataWorkbook.Worksheets("List1").Range("A3:F3", Range("A3").End(xlDown))
Dim resultWorkbook As Workbook
Set resultWorkbook = Application.Workbooks.Open("Y:\plan_graphs\final\mich_alco_test\result.xlsx")
copyRange.Copy
resultWorkbook.Worksheets("1").Range("A3").PasteSpecial Paste:=xlPasteFormulas
Dim nextRange As Range
Set nextRange = resultWorkbook.Worksheets("1").Range("A3:F3", _
resultWorkbook.Worksheets("1").Range("A3").End(xlDown)).Offset(1, 0).Select
End Sub
What am I doing wrong?
Upvotes: 0
Views: 928
Reputation: 33692
You can't Set
the range and Select
it in the same line, try the code section below:
copyRange.Copy
With resultWorkbook.Worksheets("1")
.Range("A3").PasteSpecial Paste:=xlPasteFormulas
Dim nextRange As Range
Set nextRange = .Range("A3").End(xlDown).Offset(1, 0) ' set the Range first
nextRange.Select ' <-- select the Range
End With
End Sub
Upvotes: 3