CODEWITHSUNDEEP
vbacopyrowpaste
Yesbol
Yesbol

Reputation: 1

Copy entire row if the column contains any value of another column in VBA

I'm new in VBA. I have 3 sheets: 'Sunday', 'coords' and 'filtered'. I want to check if 'A' column of the sheet 'Sunday' is equal any of values in the column 'J' of 'coords' sheet. If TRUE - copy the row in the 'filtered' sheet.

So far I have tried the following code:

Sub CopyRow()

Dim lastRow As Long
Dim sheetName1 As String
Dim sheetName2 As String
Dim sheetName3 As String

    sheetName1 = "Sunday"            'Insert your sheet name here
    sheetName2 = "coords"
    sheetName3 = "filtered"
    lastRow = Sheets(sheetName1).Range("A" & Rows.Count).End(xlUp).Row

    For lRow = 2 To lastRow         'Loop through all rows

        If Sheets(sheetName1).Cells(lRow, "A") = Sheets(sheetName2).Cells(lRow, "J") Then
            c.EntireRow.Copy Worksheets(sheetName3).Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If

    Next lRow

End Sub

Any help is greatly appreciated

Upvotes: 0

Views: 323

Answers (1)

A.S.H
A.S.H

Reputation: 29352

If you want to check the existence of the value at any row of column J, try this:

If Application.CountIf(Sheets(sheetName2).Columns("J"), Sheets(sheetName1).Cells(lRow, "A").Value2) > 0 Then
     Sheets(sheetName3).Range("A" & Rows.count).End(xlUp).offset(1).EntireRow.Value = Sheets(sheetName1).Rows(lRow).Value2
End If

Upvotes: 1

Related Questions