CODEWITHSUNDEEP
vbacopy-paste
Mike
Mike

Reputation: 269

Copy and paste unknow range into a different worksheet in excel using vba

I am trying to write a code to copy an unknown number of rows and paste it into a specific location in a separate worksheet. So far I have the code seen below.I Want to copy the data from columns A:F, for an unknown number of rows, and paste it starting in H6. I get an error in the code "Range("A1", lastrow).Select". The error is "Method range of object worksheet failed". All help is appreciated.

Dim lastrow As Long
Dim copyrange As Range

lastrow = Range("A65536").End(xlUp).Select
 Range("A1", lastrow).Select
    Selection.Copy
    Sheets("Final").Select
    Range("H6").Select
    ActiveSheet.Paste
End Sub

Upvotes: 1

Views: 14448

Answers (1)

David Zemens
David Zemens

Reputation: 53623

If you were to debug this, you would dicsover that the value of lastRow is -1. Get rid of the .Select there (and everywhere, for that matter). You also have an error in your range.Copy which I fix:

Sub Test()
Dim lastrow As Long

   lastrow = Range("A65536").End(xlUp).Row
   Range("A1:F" & lastrow).Copy Destination:=Sheets("Final").Range("H6")

End Sub

Or, to just transfer the values, I think this will do it (untested):

Sub Test2()
Dim copyRange as Range
   Set copyRange = Range("A1:F" & Range("A65536").End(xlUp).Row)
   With copyRange
       Sheets("Final").Range("H6").Resize(.Rows.Count, .Columns.Count).Value = .Value
   End With
End Sub

Upvotes: 4

Related Questions