0

I copy and paste a table of external links onto another table as only values.

I need to continue this process without overwriting the old data. The code is pasting the new data over the old data even though I added in the "lastrow" comment.

I used this before when I use the normal paste code, but this is my first time trying to use pastespecial.

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

How do I paste the data in the next available row?

Community
  • 1
  • 1
Rhiannon Roberts
  • 1
  • 1
  • Can you also provide your line of code `lastrow = ...`? – BigBen May 18 '22 at 19:00
  • It's on the 6th and 12th lines of my code above – Rhiannon Roberts May 18 '22 at 19:01
  • 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
  • 1
    `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
  • 1
    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

1 Answers1

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

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