Code to Find & Paste from one sheet to another.

[Flower R]

Hi,
I have a worksheet named “Record” . It contains almost 15 thousand plus entries in two Columns A & B and serves as Library. All entries are in text formatted cells consisting of digits and text . Daily I have to pick up about 100 to 200 entries and then copy them in a different sheet to work at. I use the excel built in facility “Find & Replace” with match Case box ticked in, search the relevant entry, copy and then paste it in a working sheet. Although it is done but very laborious because every time I have to go through “Find & Replace Box”, and switch from “record” to the working sheet and then back to “record”. I wish to have a code whereby I can work in another sheet and when type a word or figure in its cell a “suggestion box” from worksheet “record” may appear and by clicking or double clicking the relevant entry from “suggestion box” it may be pasted in the working sheet. For example, if I am working in Sheet1, and type in A1 the word “Com” , the suggestion box may indicate all words starting from Com….. , then I select the required word like “commence” and on clicking this may be pasted in A1. After that in A2, I can find and paste another value and so on. Thanks.
Flower R

[PetrH]

Hi,
first of all, so huge number of record is good reason to use some database program (MS Access) instead of Excel. Nevertheless, we can try to write some code. Could you attach example worksheet? Do you copy the data everytime to the same file or to many different?
Let us know.
Regards
Petr

[Flower R]

Thanks for reply Mr. PetrH,

Data is copied always in the same sheet and after printing the data is deleted. Again the next day in the same file the work is done and data is retrieved from "record" worksheet.

Example of "record" works sheet is:

A B

1 2345 Work

2. 1234 Go etc. etc.


Thanks again.

Flower R.

[ravishankar]

Hi
I am not clear of what you are looking for. Here is a start. try the macro in the attached file. Enter the search string or a part of it and run the macro. it will list entries containing search term.
Ravi

[PetrH]

Hi Flower R,
just one more question: why is not possible to sort the data in worksheet Record alphabeticaly? This would make the task much easier.
Petr

[Flower R]

Hi Flower R,
just one more question: why is not possible to sort the data in worksheet Record alphabeticaly? This would make the task much easier.
Petr

That was not possible. Anyhow, on your advice I switched over to Access. Thanks.

Thanks also Mr. Ravi, although your example was fine, but it did not serve my purpose as stated in my first post. I have changed file from excel to Access. Hope it will work. Thanks a lot.

Flower R