Find Data and Copy to a New sheet

[edbhome]

I am trying to write a macro (and then create a button on the sheet) that will prompt the user for the "Data/Value" they wish to find on the sheet and then search the sheet and copy all the rows with the "Data/Value" onto a new sheet.

Any help would be appreciated.

Ed

[watersev]

post sample workbook

[edbhome]

Here is a piece of the file I'm working with.

Its a Ledger output from an accounting program and we are looking to locate data and copy it to a new sheet to determine payments to individuals/companies throughout the entire ledger. (There are thousands of entries/Rows)

Example ... Press the macro button ... you get a prompt for the text string (can a parital be used or does it have to be an exact match?) ... insert "jim" ... and then the macro scans the entire sheet ... finds all the instances of "jim" ... and copies the entire row with "jim" in it ... onto a new sheet (whatever the next sheet would be). If I can have the sheet named the "text string" even better.

Thanks in advance,

Ed

[watersev]

save attachment and make sure the following reference is on:

1. [Alt+F11] - open Visual Basic window
2. Tools - References - find and tick "Microsoft Activex Data Objects 2.0 Library"
3. press "Search for"

I did not understand why rows 7-8 are empty, it's better to avoid that

[edbhome]

Thanks bunches .... I can't avoid blank rows unfortunately. Is there any way to make it not case sensitive and have the output create a new sheet. As it's written, if I do a search for Jim and then do a search for Lights ... it overwrites the sheet that comes after the tfirst one.

If not.. hey .. I'll make do cause at least I can find what I'm after and then insert a new sheet and name the output sheet something before I do the next search.

Again,

thanks for what you've done so far

[watersev]

save attachment and make sure the following reference is on:

1. [Alt+F11] - open Visual Basic window
2. Tools - References - find and tick "Microsoft Activex Data Objects 2.0 Library"
3. press "Search for"

Search results go to a new sheet. The sheet name is the search string. The search is done by columns B, C, D and E. If you are satisifed please mark the thread as solved.

[edbhome]

You are awsome !!! ... one last thing ... if you can expand the search to include columns "a - p" (for a couple of colums I didn't include in the sample I sent you) I'd be delighted.

Again, you're awsome and thanks for everything.

Ed

[watersev]

earlier comments re library ActiveX apply, added search for columns [a:p]. New data has been added to file for testing purpose. If you are satisifed please mark the thread as solved.

[edbhome]

Once again,

You're AWSOME !!! ... it's perfect and I thank you ... my people thank you ... and the people that are scanning 32,000 lines of payments and invoices for stuff ... thank you.

Solved !!!

[edbhome]

I can't remember how to mark a thread "Solved" ... "Help" !!