Microsoft Excel 2007 Find rows and copy to new sheet

Steven Debock
Steven Debock used Ask the Experts™

I get XSLX files from a distributer with over 30.000 rows
In this file there is a column called CatagoryID.
I want to be able to enter a string somewhere containing my desired catagoryID
That will copy all rows with correct catagoryID  to sheet2
Giving me a filterd sheet with my search results.
How can i achieve this ?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Where should the string be entered? Is it always the same column? Can you post a small sample workbook?
Is this what your looking for ? Just a smaple on sheet1 and press the command button and chk the answer. Pls let me know we can cater as you feel appropriate



It would be preferd if the string could be enterd in an inputbox.
CatagoryID is always the in same column.

you can see the file @


Thats the ting i need, but then to let it copy the full rows that you can find in the url above
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Maybe something along these lines:
Sub x()
Dim rFind As Range, sFind As String, sAddr As String
sFind = Application.InputBox("Search string?", Type:=2)

With Sheets("Price_Generic").Columns(7)
    Set rFind = .Find(What:=sFind, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        sAddr = rFind.Address
        Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Results"
            rFind.EntireRow.Copy Sheets("Results").Range("A" & Rows.Count).End(xlUp)(2)
            Set rFind = .FindNext(rFind)
        Loop While rFind.Address <> sAddr
    End If
End With
End Sub

Open in new window

Sorry for being late to answer. here is my solution exactly as posted first but adapted to your file. Pls press on the button in col G1 and chk the results.
tks gowflow
Thanks for not bothering to even acknowledge my solution.


You are right.....i blame time.
Just tried your solution and it works just fine.
Any way to regrade a closed question ?
You need to post a REQUEST ATTENTION at the top of this question and explain why you need to re-open the question. Once moderatorre-opens it again, you can then re-assign ponts as you feel appropriate. For next time you should carefully check all answers and attribute points fairly.

I do not mind personally to have this question re-opened and you attribute points as you feel is fair.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial