Separate required records from a file, MAK

Solved/Closed
MAK - Mar 2, 2010 at 12:22 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 4, 2010 at 07:34 AM
Hello,


I am using following macro to separate required records from a file contain several worksheets and each worksheet contain thousands of records. I am facing following problems:

1) Have to target each worksheet one by one (Is it possible that this macro search the required records in one go from all worksheets of same Excel file)

2) I have to copy separated records in another worksheet and have to give them a name (Is it possible that this macro automatically copy the separated records in a new worksheet and give it name same as searched e.g. china, India etc in same or in a separate file.)

3) Most importantly I have to search single word or part of a word in a cell contain big string of text e.g cell contain : pharmaceutical raw material Amoxicillin powder ......... I have to separate the records contain word or part of word pharma or pharmaceutical or Amox or Amoxicillin etc by using wildcard (*,?) and specifying column. Is it possible?

4) I have to copy the heading of column first to the worksheet where the separated records will be copied through this macro (Is it possible that it automatically copy the heading of the columns)

I will be very grateful if some one help me and amend the following macro as per my requirement along with the comments in front of each command

Sub search()

Set i = Sheets("January 2010")
Set e = Sheets("sheet1")
Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(i.Range("c" & j))

If i.Range("c" & j) = "China" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value

End If
j = j + 1
Loop

End Sub

MAK
Related:

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 2, 2010 at 06:56 AM
Question #1

You can use

For Each mySheet in Sheets

<<you code goes here>>

next

Question #2

Sheets.Add
ActiveSheet.Name = mySearch

Question #3,
Look at this code done by Venkat.
https://ccm.net/forum/affich-260728-how-to-create-macro-to-search-copy-paste

Question#4
just copy the first row and paste it via code. Use macro recorder if you have any doubt
0
Dear Rizvisa1,

Thank you for the reply and help.

I asked you to please ammend the macro for me according to my requirements by giving comments infront of each command.

I am not very much usetoo with macros although I have made some small macros to execute some samall jobs but I have to learn more and I think you can help me out.

I will be greatful to you if you could send me your personal email address.

Thank you,

MAK
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > MAK
Mar 3, 2010 at 10:40 AM
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
0
MAK > rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Mar 3, 2010 at 11:42 PM
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > MAK
Mar 4, 2010 at 07:34 AM
#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
0