VBA - Find Specific words from Column "A" and paste to column "H"

[sanju2323]

need find specific word "TP", "IL" column "A" to Copy range "B:F" and past to Column "H"

[jolivanes]

You could try this on a copy of your workbook.

Sub Filter_Multiple_Values()
Application.ScreenUpdating = False
If Sheets("Sample").AutoFilterMode = True Then Sheets("Sample").AutoFilterMode = False
Sheets("Sample").Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter Field:=1, Criteria1:=Array("IL", "TP"), Operator:=xlFilterValues
Range("B3:F" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(12).Copy Range("H3")
Sheets("Sample").AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

[sanju2323]

Thank you sir, It works great

[sanju2323]

Sir, Is that possible generate (paste) Data other sheet ex. Sheets("Sheet2").Range("H3")
and all Paste Special value format Ex .PasteSpecial xlPasteValuesAndNumberFormats

[jolivanes]

When you used the macro recorder, what did you get?
Post that code if it does not work so we help you cleaning it up.

[sanju2323]

Sir, Code is work no issue, but this code show result as just like cut past formate. I need past special value format.

[jolivanes]

Like this?

Sub Filter_Multiple_Values_A()
Application.ScreenUpdating = False
If Sheets("Sample").AutoFilterMode = True Then Sheets("Sample").AutoFilterMode = False
Sheets("Sample").Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter Field:=1, Criteria1:=Array("IL", "TP"), Operator:=xlFilterValues
Range("B3:F" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
With Sheets("Sheet2").Range("C2") '<---- Change Sheet Name and Cell reference as required
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
Sheets("Sample").AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

[sanju2323]

Sir, Thank you for cooperation. this time its perfect work as per my requirement.

[jolivanes]

Thank you for letting us know.
Good Luck