Paste in next row of table when using paste special

Question

I am successfully copying and pasting a table of external links onto another table as only values, however I need to continue this process without overwriting the old data. The code is not receiving any errors, but when I run it, it's pasting the new data over the old data still even though I added in the "lastrow" comment. I've used this before when I use the normal paste code, but this is my first time trying to use pastespecial so I suspect it has to do with that, but I have not been able to figure out what to do. My code is below:

Sub Check() 'compares old and new values
    If Worksheets("Test").Range("N2").Value <> Worksheets("Test").Range("N5").Value Then
        Worksheets("Test").Activate
        Worksheets("Test").Range("TestTable").Select
        Selection.Copy
        Worksheets("Sheet1").Activate
        Worksheets("Sheet1").Range("Destination" & lastrow).PasteSpecial xlPasteValues
    ElseIf Worksheets("Test").Range("O2").Value <> Worksheets("Test").Range("O5").Value Then
        Worksheets("Test").Activate
        Worksheets("Test").Range("TestTable").Select
        Selection.Copy
        Worksheets("Sheet1").Activate
        Worksheets("Sheet1").Range("Destination" & lastrow).PasteSpecial xlPasteValues 
    End if

Can someone please help me figure out what I am missing to paste the data in the next available row?

There is no line in your code that begins `lastrow = ...`. In other words, where/how are you assigning a value to the variable `lastrow`? — BigBen, May 18 '22 at 19:02
When I mentioned last row, I was talking about adding it inside the range before paste — Rhiannon Roberts, May 18 '22 at 19:03
`lastrow` is a variable. You need to assign it a value. For example, [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) is the canonical answer on SO treating how to find the last row. If you want to find the last row in a table, see [this](https://stackoverflow.com/questions/43631926/lastrow-and-excel-table). — BigBen, May 18 '22 at 19:04
Side note: you want to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, May 18 '22 at 19:30
If you're using an excel table already you can refer to it instead. Then you can get the excel row number of the final row that is part of your table `Listobjects("YourTable").rows(Listobjects("YourTable").rows.count).row` then add 1 to get the row below that and paste there. Also as cybernetic said select and activate are unneeded `Worksheets("Test").Range("TestTable").Copy` followed by your paste should accomplish the same thing. — Basbadger, May 18 '22 at 19:44

score 0 · Answer 1 · answered May 18 '22 at 19:36

0

If you have not deemed "lastrow" as a variable then the code will not work. "lastrow" in it's self is not a Visual Basic Command, you have to make it one yourself first.

Dim lastrow As ______

How I find a last row is usually by doing something like this:

Range(Range("W1"), Range("Y1").End(xlDown)).select

This is an example of selecting the range from W1:Y____. The blank being the last row in the column with a value in it. The ".End(xLDown) means that it looks downwards for the end of the list of values in the column. I hope this helps

answered May 18 '22 at 19:36

OppostoCopernicus

13
5

Please consider reading [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba), which points out the limitations of `End(xlDown)` and proposes a more robust approach. – BigBen May 18 '22 at 19:38
1

As you can see I am a new contributor, so this is very helpful. Thank you @BigBen – OppostoCopernicus May 18 '22 at 20:33

Paste in next row of table when using paste special

1 Answers1