Copy a column of data from the second row until the last row with data in it and paste as the last row in a column on another sheet VBA

Question

I am working on a VBA code to copy data from the second cell "A2" down to the last cell with data in it (this range will change). I then want to paste that data starting at the last row of column A on another sheet.

My VBA code as of now seems to do nothing. Can someone help me figure out why?

Option Explicit

Public Sub AddNotes()

Dim lastRow1 As String
Dim lastRow2 As String

lastRow1 = Sheets("PropertiesToMatch").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("PropertiesToMatch").Range("A2" & lastRow1).Copy

lastRow2 = Sheets("PropertyNotesToUpload").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("PropertyNotesToUpload").Range("A" & lastRow2).PasteSpecial Paste:=xlPasteValues

End Sub

You may want to change `Dim lastRow1 As String` and `Dim lastRow2 As String` to `As Long`? — Zack E, Nov 14 '19 at 19:05
@ZackE that is a good catch. I will incorporate into my answer. — BigBen, Nov 14 '19 at 19:05
Just one more observation. I believe it would be more prudent to qualify the sheets and ranges and instead of copying them make the ranges equal to each other from a performance standpoint. Granted without knowing how large the data set is it may not matter. — Zack E, Nov 14 '19 at 19:10

BigBen · Accepted Answer · 2019-11-14T19:06:22.067

3

Change

.Range("A2" & lastRow1)

to

.Range("A2:A" & lastRow1)

The first is only one cell, the second is the range you want.

Also, change

Dim lastRow1 As String
Dim lastRow2 As String

to

Dim lastRow1 As Long
Dim lastRow2 As Long

because Range.Row is a Long, not a String.

edited Nov 14 '19 at 19:06

answered Nov 14 '19 at 19:00

BigBen

46,229
7
24
40

score -2 · Answer 2 · answered Nov 14 '19 at 19:17

-2

Option Explicit

Sub CopyPaste()
Dim i, j As Integer
Dim LastRow As Integer

With Sheets("PropertiesToMatch")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
j = 2

For i = LastRow To 2 Step -1
Sheets("PropertiesToMatch").Cells(i, 1).Copy
Sheets("PropertyNotesToUpload").Cells(j, 1).PasteSpecial Paste:=xlPasteValues

j = j + 1
Next i

End Sub

answered Nov 14 '19 at 19:17

q0mlm

313
1
3
10

There are lots of things wrong with this answer. Use `Long` instead of `Integer`, no loop is needed, and you're looping from the bottom to the top, among others. – BigBen Nov 14 '19 at 19:27
Thanks for your corrections. Genuinely curious, why use Long instead of Integer? As for the reversed loop I interpreted the "I then want to paste that data starting at the last row of column A on another sheet" as I want the last cell of column of A in the first sheet to be the first cell of column A in the second sheet. – q0mlm Nov 14 '19 at 20:02
I can see how you might take that away from the question, but OP's code is pretty clear what the goal is, and there's an accepted answer already. – BigBen Nov 14 '19 at 21:01
See [this question](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) for why not to use Integer. Main reason is that there's a possible Overflow error - Excel has more rows than Integer can handle. – BigBen Nov 14 '19 at 21:02
Apologies if my first comment came off as rude in any way, I could have been more gentle about it. – BigBen Nov 14 '19 at 23:05

Copy a column of data from the second row until the last row with data in it and paste as the last row in a column on another sheet VBA

2 Answers2