Search column for text "X" then copy to

[Closed]
Report
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
Hello,
I would like to have a macro to search a column B for text "X". Wherever text "X" is found in that column, I want to copy text "X" and the corresonding cell in that row in column A. I want to transfer each case to a different worksheet.

Column A Column B
Question 1 A
Question 2 A
Question 3 D
Question 4 F
Question 5 X
Question 6 E
Question 7 W
Question 8 X

Result on different worksheet
X Question 5
X Question 8

Thanks


1 reply

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Shannon,

Try this code:
Sub test()
Dim lRow, lRow2 As Integer
lRow = Sheets("Main").Range("B" & Rows.Count).End(xlUp).Row
    For Each cell In Sheets("Main").Range("B2:B" & lRow)
        If cell.Value = "X" Then
lRow2 = Sheets("Dest").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
cell.Copy Destination:=Sheets("Dest").Range("A" & lRow2)
cell.Offset(0, -1).Copy Destination:=Sheets("Dest").Range("B" & lRow2)
        End If
    Next cell
End Sub

Please adjust sheet names ("Main" and "Dest") to match yours.

Best regards,
Trowa