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
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.
