VBA Paste at Last Row + 1 -- Not Working

[MarkRCC]

Hi All,

I have used VBA to paste a range below the last active cell in the paste to good success, but for some reason I cannot seem to get it to work in this instance.

I have two named ranges that I am trying to move into one single column, but rather than building a combined list, the second range is overlapping on the first.

The code I am using is as follows:

Sub GeneratePlantList()
Dim lr As Long
Set ws = ThisWorkbook.Sheets("Index")
Set dom = ThisWorkbook.Sheets("Domain")

lastRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row

ws.Range("TreeRange").Copy
dom.Range("F" & lastRow + 1).PasteSpecial Paste:=xlPasteValues

ws.Range("TreeRange2").Copy
dom.Range("F" & lastRow + 1).PasteSpecial Paste:=xlPasteValues

The first "TreeRange" is going below the column header fine, and then "TreeRange2" pastes into the same spot below the header, rather than at the end of the first "TreeRange"

What am I doing wrong this time?

Thanks in advance

 

[Leith Ross]

Hello MarcRCC,

You need to recalculate the Last Row after you paste the first time. As a side note, you rarely need to select objects in VBA before performing an operation. Selecting greatly slows do your code.

Sub GeneratePlantList()


    Dim lr As Long


        Set ws = ThisWorkbook.Sheets("Index")
        Set dom = ThisWorkbook.Sheets("Domain")


        lastRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row


        ws.Range("TreeRange").Copy
        dom.Range("F" & lastRow + 1).PasteSpecial Paste:=xlPasteValues


        lastRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
        ws.Range("TreeRange2").Copy
        dom.Range("F" & lastRow + 1).PasteSpecial Paste:=xlPasteValues