search column for text "X" then copy toClosed

Question

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


Configuration: Windows XP / Internet Explorer 8.0

TrowaD · Answer

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

Search column for text "X" then copy to

1 response

Similar discussions

Subscribe To Our Newsletter!