I am trying to select an entire row in a different sheet and then copy the row to the sheet I am currently in with a macro. The code works fine if the Rows() sub is passed integers hardcoded but when I put a variable I get the "Select method of Range class failed" error. here is the code I have:
Sheets("BOM").Select
Rows(copyFromRow & ":" & copyFromRow).Select
Selection.Copy
Sheets("Proposal").Select
Rows(copyToRow & ":" & copyToRow).Select
ActiveSheet.Paste
copyToRow = copyToRow + 1
Rows(copyToRow & ":" & copyToRow).Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
if instead i used :
Rows("52:52").Select
Selection.Copy
it works fine, but when the variable is there, the error occurs.
Thanks
The answer is
I just tested the code at the bottom and it prints 16384 twice (I'm on Excel 2010) and the first row gets selected. Your problem seems to be somewhere else.
Have you tried to get rid of the selects:
Sheets("BOM").Rows(copyFromRow).Copy
With Sheets("Proposal")
.Paste Destination:=.Rows(copyToRow)
copyToRow = copyToRow + 1
Application.CutCopyMode = False
.Rows(copyToRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With
Test code to get convinced that the problem does not seem to be what you think it is.
Sub test()
Dim r
Dim i As Long
i = 1
r = Rows(i & ":" & i)
Debug.Print UBound(r, 2)
r = Rows(i)
Debug.Print UBound(r, 2)
Rows(i).Select
End Sub
One needs to make sure the space between the variables and '&' sign. Check the image. (Red one showing invalid commands)
The correct solution is
Dim copyToRow: copyToRow = 5
Rows(copyToRow & ":" & copyToRow).Select
You need to add quotes. VBA is translating
Rows(copyToRow & ":" & copyToRow).Select`
into
Rows(52:52).Select
Try changing
Rows(""" & copyToRow & ":" & copyToRow & """).Select
The key is in the quotes around the colon and &, i.e. rows(variable & ":" & variable).select
Adapt this:
Rows(x & ":" & y).select
where x and y are your variables.
Some other examples that may help you understand
Rows(x & ":" & x).select
Or
Rows((x+1) & ":" (x*3)).select
Or
Rows((x+2) & ":" & (y-3)).select
Hopefully you get the idea.
I solved the problem for me by addressing also the worksheet first:
ws.rows(x & ":" & y).Select
without the reference to the worksheet (ws) I got an error.
Saw this answer on another site and it works for me as well!
Posted by Shawn on October 14, 2001 1:24 PM
var1 = 1
var2 = 5
Rows(var1 & ":" & var2).SelectThat worked for me, looks like you just have to keep the variables outside the quotes and add the and statement (&)
-Shawn
Source: Stackoverflow.com