Copy and paste user defined range

Question

Can someone please help me copy and paste a user defined range. I have the below but for some reason am getting an error when trying to perform the action. I suspect I am missing one tiny change. Thanks.

Sheets("Sheets1").Select
Dim lngLastRow2 As Long

lngLastRow2 = Cells(Rows.Count, "A").End(xlUp).Row

Worksheets("Sheets2").Range("A3:A" & lngLastRow).Copy _
Destination:=ActiveSheet.Range(lngLastRow2)

You need a column reference in your destination range, currently you just have a row. — SJR
– SJR, Commented Mar 13, 2017 at 15:39

CallumDA · Accepted Answer · 2017-03-13 15:49:35Z

1

You do have an error here:

Destination:=ActiveSheet.Range(lngLastRow2)

Which evaluates to .Range(x) where x is some Long. What Range needs is something like .Range("A20").

One solution is to change it to

Destination:=ActiveSheet.Range("A" & lngLastRow2)

Or another solution is to use a Range variable instead:

Dim lastRow As Range
Set lastRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)
With ThisWorkbook.Worksheets("Sheet2")
    .Range("A3:A" & lastRow.Row).Copy Destination:=.Range(lastRow.Address)
End With

answered Mar 13, 2017 at 15:49

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Danny Over a year ago

Based on what I got from you it only copied over a single column of data. What I am trying to do is determine the last row of data on Sheet 1 (store that), determine the last row on Sheet 2, then copy all data from beginning through the last row on Sheet 2 into Sheet 1 starting at the first blank row. I obviously need all columns that have data on sheet 2 and not just the first column. I have thought about determining the last row of data and then the last column of data as noted below, but it seems I am making this more complicated then it needs to be.

Collectives™ on Stack Overflow

Copy and paste user defined range

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related