[Solved] How to copy cells that contain certain text to another worksheet [closed]

by


As per comments I’ve amended the code to copy only the specified range, both Sheets should exist, the code will not create the second Sheet for you:

Sub Test()
Dim Cell As Range

With Sheets("Sheet1") 'Sheet with data to check for value
    ' loop column H untill last cell with value (not entire column)
    For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
        If Cell.Value = "1234" Then
            NextFreeRow = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row + 1
             'get the next empty row to paste data to
            .Range("A" & Cell.Row & ",B" & Cell.Row & ",C" & Cell.Row & ",F" & Cell.Row).Copy Destination:=Sheets("Sheet2").Range("A" & NextFreeRow)
        End If
    Next Cell
End With
End Sub

UPDATE:

The code below will search for the text “1234” inside each cell in column H, if found then it will copy your desired range.

Sub Test()
Dim Cell As Range

With Sheets("Sheet1") 'Sheet with data to check for value
    ' loop column H untill last cell with value (not entire column)
    For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
        pos = InStr(Cell.Value, "1234")
        If pos > 0 Then
            NextFreeRow = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row + 1
             'get the next empty row to paste data to
            .Range("A" & Cell.Row & ",B" & Cell.Row & ",C" & Cell.Row & ",F" & Cell.Row & "," & Cell.Address).Copy Destination:=Sheets("Sheet2").Range("A" & NextFreeRow)
        End If
    Next Cell
End With
End Sub

9

solved How to copy cells that contain certain text to another worksheet [closed]