Microsoft Excel 2007 Find rows and copy to new sheet

Steven Debock
Steven Debock used Ask the Experts™
on
Hello,

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 ?

Comment
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
gowflow
Autofilter.xlsm

Author

Commented:
@StephenJR

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 @ http://www.dbits.be/list.xlsx

@gowflow

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"
        Do
            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
list.xlsm
Thanks for not bothering to even acknowledge my solution.

Author

Commented:
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.
Rgds/gowflow

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