How to copy cells that contain certain text to another worksheet

Question

I am trying to copy cells that meet a certain criteria to a new worksheet.

For example, if Cell H15 on Worksheet(1) contains the requested value (1234), then only copy Cells A15, B15, C15, F15 and the cell containing text string to a new row on a new worksheet?

I would like to be able to scan a range, say M1:X155 and for each found value (1234) copy the above cells to a new worksheet.

Hi Tom, welcome to SO. Your question is not up to our standards, please read [ask] on how your question can be improved. Some pointers to help you get started in getting an answer: 1. You want to loop over your range M1:X155 2. You want to use an If condition to check if the value is equal to 1234. 3. You want to use another counter to keep track of which row to paste on in your second sheet. 4. You want to set the value of the cells on the second sheet equal to those on the first sheet when a match is found. — Luuklag, May 04 '18 at 14:36
If I were to do this, I would: 1. [Loop through the range you are wanting to check with a `For` loop.](https://support.microsoft.com/en-us/help/299036/how-to-loop-through-a-list-of-data-on-a-worksheet-by-using-macros-in-e) 2. [Use an IF statement to test the value in each row](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/ifthenelse-statement) 3. Copy the values over — JNevill, May 04 '18 at 14:37
I need the same as this: https://stackoverflow.com/questions/45209392/excel-how-to-copy-a-row-if-it-contains-certain-text-to-another-worksheet-vba but not the entire row, just specific cells IN that row — Tom Rundle, May 04 '18 at 14:43

Xabier · Accepted Answer · 2018-05-04T19:23:07.760

1

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

edited May 04 '18 at 19:23

answered May 04 '18 at 14:50

Xabier

7,587
1
8
20

Thank you for the useful reply. The value I am trying to locate "1234" is buried in the text of the cell. ie. "2018 book cover - UPC number 1234" How can I get the formula to locate that text string alone? – Tom Rundle May 04 '18 at 14:56
@TomRundle I've updated my answer to check if the text 1234 is within a cell in column H, if so it will copy your range to the other sheet. – Xabier May 04 '18 at 15:08
That is PERFECT! Thank you very much! If I can get greedy, is there a way to search a range instead of a single column? Say range M1:X155. – Tom Rundle May 04 '18 at 15:14
@Tom, yes just replace `.Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)` with `.Range("M1:X155")` please could you also accept my response as an answer? Thanks. – Xabier May 04 '18 at 15:15
Can the code be used to also copy the cell that contained the text string? (So copy cells A, B, C, F and the cell containing the text string) – Tom Rundle May 04 '18 at 15:34
@Tom, I've updated my last answer to include the cell where the string was found. Hope this helps. :) – Xabier May 04 '18 at 16:00
Sorry, I am receiving a Run-Time error '1004' Application-defined or object-defined error with the updated code above – Tom Rundle May 04 '18 at 16:20
@Tom, updated again, this should now work... – Xabier May 04 '18 at 19:23
Perfect! Thank you again! – Tom Rundle May 07 '18 at 11:01

How to copy cells that contain certain text to another worksheet

1 Answers1