Macro to copy row if certain value available in cell

[amittheexcel]

Hi There,

I have below macro which can be use to select rows if one value available in cell. But i want to remodify this code to select the data more than one value.

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub Test()
ForEach Cell In Sheets(1).Range("J:J")
If Cell.Value ="131125"Then
matchRow = Cell.Row
Rows(matchRow &":"& matchRow).Select
Selection.Copy

Sheets("Sheet2").Select
ActiveSheet.Rows(matchRow).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
EndIf
Next
EndSub

ex - I need to select row if cell having 131125 or 131126 or 131127.

please help</code>

 

[Joe4]

One way:

[COLOR=#101094][FONT=Consolas][FONT=inherit]If[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] (Cell[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Value [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"131125") Or [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit](Cell[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Value [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"131126") Or [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit](Cell[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Value [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"131127") [/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Then[/FONT][/FONT][/COLOR]

 

[dmt32]

hi,
untested but see if this update to your code does what you want

Sub Test()
    Dim m As Variant
    Dim MatchRow As Long
    Dim Cell As Range
    
    With Sheets(1)
    For Each Cell In .Range("J:J")
        m = Application.Match(Cell.Text, Array("131125", "131126", "131127"), False)
        If Not IsError(m) Then
            MatchRow = Cell.Row
            .Rows(MatchRow & ":" & MatchRow).Copy Sheets("Sheet2").Rows(MatchRow)
        End If
    Next
    End With
End Sub

Dave

 

[amittheexcel]

Thank you it's work