Separate required records from a file, MAK

Couple of questions and issues

1. You cannot name a sheet like Ind* or *pharma*, so if you would search like this how would u name

2. when you do a search, are you searching in a specific column (across all sheets) or you are looking in each and every column

3. Do you expect Pharma* and *pharma* to go on to same sheet

4. Could you be searching for "pharma" and "amox" in same search

5. Is this a batch search. By batch i mean, you would do all the searched and when you quit you are done with searching. Next search would be again from start. This is issue because as search was going on, new sheets have been created, and there is redundant data

Couple of questions and issues

1. You cannot name a sheet like Ind* or *pharma*, so if you would search like this how would u name

Ans.: U mean that wildcard characters will not be acceptable in the name of a worksheet, Ok I got understand it. Now I think to solve this problem I have few things in my mind let me know what will be work: 1) Is this possible that a worksheet will be created automatically for the searches words in same file or in a new file and give them a serial number e.g. 1 = *pharma*, 2= Ind* etc and give serial number as name and amend it later. Or use any other command to remove the wildcard characters after listing / giving them serial number them and rename the worksheet accordingly.

2. when you do a search, are you searching in a specific column (across all sheets) or you are looking in each and every column

Ans.: All sheets have same column headings its means that if we search column D all sheets have same heading “Origin”. I would prefer to specify the column. It will be very good if a manu will appear and ask for the search word (with wildcard characters facility) and ask to specify the column to search.

3. Do you expect Pharma* and *pharma* to go on to same sheet
Ans.: No, for each search there will be a new worksheet.


4. Could you be searching for "pharma" and "amox" in same search

Ans.: Yes, it will be very good.

5. Is this a batch search. By batch i mean, you would do all the searched and when you quit you are done with searching. Next search would be again from start. This is issue because as search was going on, new sheets have been created, and there is redundant data
Ans.: I got understand your point, to solve this problem I think we should copy searched records in a new worksheet of a new file.

I really appreciate your support, time and attention which you are extending to me.I do hope that you will create a macro according to my requirements along with comments in front of each command.
I requested you for your personal email address.
Thank you
MAK

#1, #5. Do able. How ever a question, why not have a search sheet. In col A it has search criteria used, and from B onwards, the data obtained. Of course you are limited to excel row limitation

#2, #4 If your search target is always one column ( which may vary being A at one time and X at other, but still some specific column[s]) then have a look at this

https://ccm.net/forum/affich-268778-find-a-text-in-a-sentence-inside-a-cell

Idea is to search, copy and move data can be handled later. But I think this gives you best approach for search. You can have all sort of looks up in one go. So look at the thread. See if it helps in conduction search (again copy paste etc can be handled. This is to just located the record).
https://ccm.net/forum/affich-268778-find-a-text-in-a-sentence-inside-a-cell