Selecting certain columns in VBA after searching

Question

I am trying to search for text on a sheet in column c then if found within the same row select column a and copy and paste to sheet two. i have started with this code

Sub Test()
For Each Cell In Sheets("Asset Capture").Range("C35:C3000")
If Cell.Value = "MONITOR" Then
matchRow = Cell.Row
Rows.Range(matchRow & ":" & matchRow).Select
Selection.Copy
Sheets("GRN Status Report").Select
lastRow = ActiveSheet.UsedRange.Rows.Count
If lastRow > 1 Then lastRow = lastRow + 1
ActiveSheet.Range("A" & lastRow).Select
ActiveSheet.Paste
Sheets("Asset Capture").Select
End If
Next
End Sub

but it is selecting the whole row and i can not figure out how to change the code to select data from just the A column?

Thank you all got it sorted!!! – jest3r Apr 06 '16 at 02:32 — jest3r, Apr 06 '16 at 02:32

score 0 · Answer 1 · edited May 23 '17 at 10:33

Try this:

Sub Test()
    Dim Cell As Range, rngDest As Range

    Set rngDest = Sheets("Grn Status Report").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    For Each Cell In Sheets("Asset Capture").Range("C35:C3000")
        If Cell.Value = "MONITOR" Then
            Cell.EntireRow.Cells(1).Copy rngDest
            Set rngDest = rngDest.Offset(1, 0)
        End If
    Next

End Sub

Note you don't need to use Select/Activate, and your code will be more robust if you avoid it as much as possible.

See: How to avoid using Select in Excel VBA macros

score 0 · Answer 2 · answered Apr 05 '16 at 06:31

Hope you looking for this

Sub Test()
increment = Worksheets("GRN Status Report").Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Asset Capture").Range("C5:C3000")
    If cell.Value = "MONITOR" Then
        matchrow = cell.Row
        matchcontent = Range("A" & matchrow).Value
        Worksheets("GRN Status Report").Cells(increment, 1) = matchcontent
        increment = increment + 1
    End If
Next
End Sub

Selecting certain columns in VBA after searching

2 Answers2