find copy and paste

[Np72]

Hi,
IS there anyway that excel will find text , copy this and place in a difference worksheet

From my results page I'm looking to find the text "check Disclosure" in Col K and copy the whole row into a another worksheet labelled front. Check disclosure appears multiple times in the work sheet

 

[GlennUK]

Advanced Filter will do this in one operation. You need to set up criteria for column K before processing. Have you used Advanced Filter before? Does this sound like an acceptable solution?

 

[Np72]

Hi Glenn,

I'll give it a go.

He who dares rodders

 

[GlennUK]

I generally have my criteria in a separate sheet, and in your case put the heading from column K of the data into cell A1, and the value

check Disclosure

into cell A2.

Into a third sheet copy the headers from the data.

Then use this macro:

Sub MyFilter()
    Sheets("Data").Range("A1:M25").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Worksheets("MyCrit").Range( _
        "A1:A2"), CopyToRange:=Worksheets("MyResults").Range("A1:M1"), Unique:=False
    Worksheets("MyResults").Activate
End Sub

... changing the sheet names and ranges to suit.