Paste if not "" and after last row in VBA

Question

I there I have the following problem: I would like to paste the results in a new sheet if the outcome is not "NO MATCH", how can I paste this in the new sheet and after the last used row? I get an error on the Active.Paste

Here is my code:

Public Sub CopyRows()
    Sheets("Koppeling data").Select
    ' Find the last row of data
    FinalRow = Cells(Rows.Count, 4).End(xlUp).Row
    ' Loop through each row
    For x = 3 To 10
        ' Decide if to copy based on column D
        ThisValue = Cells(x, 4).Value
        If ThisValue = "NO MATCH" Then

        Else
            Rows(x).Copy
            Sheets("All sessions").Select
            Call FindingLastRow
            ActiveSheet.Paste
            Sheets("Koppeling data").Select
        End If
    Next x
End Sub


Sub FindingLastRow()
    Dim sht As Worksheet
    Dim LastRow As Long

    Set sht = ThisWorkbook.Worksheets("All sessions")

    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
End Sub

It doesn't work correctly, because it doesn't paste after the last row, but it just paste the 10th row from "Koppeling data" sheet 10 times at the "All sessions" sheet — Sner88, Mar 02 '16 at 15:36
I provided an answer below, but so you understand why this code does not work the `ActiveSheet.Paste` is directly set after the worksheet is selected. The code never specifies **exactly** where to paste. You do call `FindingLastRow` but this just sets the last row of data to the variable `LastRow`. It does not select any cell, nor assign a range to work with. — Scott Holtzman, Mar 02 '16 at 15:42

Scott Holtzman · Answer 1 · 2016-03-14T14:24:12.640

2

Give this a shot. I simplified the code a lot, removed the .Select statements - which should be avoided at all costs- , and assigned variables to objects and worked directly with them.

Public Sub CopyRows()

Dim wsK As Worksheet, wsA As Worksheet
Set wsK = Sheets("Koppeling data")
Set wsA = Sheets("All sessions")

Dim FinalRow as Long
FinalRow = wsk.Cells(wsk.Rows.Count, 4).End(xlUp).Row

' Loop through each row in Koppeling data
For x = 3 To FinalRow

    ' Decide if to copy based on column D
    If wsK.Cells(x, 4).Value <> "NO MATCH" Then
        wsK.Rows(x).EntireRow.Copy _
            Destination:=wsA.Range("A" & wsA.Rows.Count).End(xlUp).Offset(1) 'used `.Offset(1)` here so it will paste one row below last row with data.
        'use this to paste values
        'wsk.Rows(x).Copy
        'wsA.Range("A" & wsA.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End If

Next x

End Sub

edited Mar 14 '16 at 14:24

answered Mar 02 '16 at 15:38

Scott Holtzman

27,099
5
37
72

How can I set the 10 in the for loop to the final Row of wsK? – Sner88 Mar 02 '16 at 15:53
Hi Scott, how can I paste the values instead of the formulas? – Sner88 Mar 14 '16 at 14:18
Hi Scott, how can I paste the values instead of the formulas? Seems like it takes the wrong row from the 'Koppeling data' sheet – Sner88 Mar 14 '16 at 14:25

score 0 · Answer 2 · answered Mar 02 '16 at 15:44

You have FindingLastRow as sub, which doesn't do anything. If you want to return the result (in your case the last row), you have to define it as a function:

Function FindingLastRow() as Long
     'Your existing code
     FindingLastRow = LastRow
End Function

This will return the value of last row, and in the main sub you can just paste into the following row:

Dim lastRow as Long
lastRow = FindingLastRow
ActiveSheet.Range("A" & lastRow + 1).Paste

score 0 · Answer 3 · edited May 23 '17 at 12:15

Try to get away from using Worksheet.Select and Range .Select to accomplish your actions.

Public Sub CopyRows()
    Dim x As Long, lastRow As Long, finalRow As Long

    With Worksheets("Koppeling data")
    ' Find the last row of data
        finalRow = .Cells(.Rows.Count, 4).End(xlUp).Row
        ' Loop through each row
        For x = 3 To 10 'For x = 3 To finalRow
            ' Decide if to copy based on column D
            If UCase(.Cells(x, 4).Value) <> "NO MATCH" Then
                FindingLastRow Worksheets("All sessions"), lastRow
                .Rows(x).Copy Destination:=Worksheets("All sessions").Range("A" & lastRow + 1)
            End If
        Next x
    End With
End Sub

Sub FindingLastRow(ws As Worksheet, ByRef lr As Long)
    With ws
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
End Sub

The ByRef allows you to pass a previously declared variable into your helper sub and have that variable return with a changed value.

_{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

Paste if not "" and after last row in VBA

3 Answers3