Runtime error '1004' : You cant past this here cause the Copy area and past area arent the same size

Question

I have a macro that copies a list of numbers from column A on the first sheet, and pastes it starting at the first blank cell in column A on the second sheet.

Sheets("TNF").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("TNF Check").Select
Range("A1").End(xlDown).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Basically, it uses the CTRL+SHIFT+Down function to select everything from A2 down. It works fine when there is more than one cell to copy. But when there is only one cell, it grabs the entire column A (1048576 cells) and tries to paste it all on the second sheet, which doesnt fit (data is already there). How can i update the code to not grab the whole column, but rather, only grab cells with data actually in it?

Answer 1

Use xlup:

Sheets("TNF").Range("A2",Sheets("TNF").Range("A" & Rows.Count).End(xlup)).Copy
Sheets("TNF Check").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

Or better avoid the clipboard when only wanting values:

Dim rng as Range
Set rng = Sheets("TNF").Range("A2",Sheets("TNF").Range("A" & Rows.Count).End(xlup)).Value
Sheets("TNF Check").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(rng.Rows.Count,1).Value = rng.Value