[PincivMa]

Hi there

I know that this is a stupid question, since I can easly do it manually by filtering data. I have a client who is not computer savi. She wants me to do a program for her that filters her data. Now get this!! She wants to put the name of the data to be filtered in cell A1. Say that she wants to filter all the last names called "Smith". So in Cell A1, she types in Smith. All the last names are in column C. Then she wants to click on a button to run the macro that filters the data. Is there a macro that can pick up the word Smith in cell A1and somehow use that name to filter the data in column C??

Mario

[Aj_old]

It could be done, but:
1. She wanna always filter by last name?
2. She wanna filter only by one name or criteria, or it can be multiple names, multiple criteria?
3. What's the range to be filtered? Is it always the same?
4. The data structure is always the same?
5. Is it only one file, or she would like to use it for more files?
6. How about using auto filter, it is very easy and powerful?

[PincivMa]

She wants to type a first name in Cell A1 and a last name in Cell B1. These names change depending on which name she wants to filter. The list of first names are in Column C and the list of last names are in Column D. There is other data in columns E,F,G and H. She wants to filter by first name first and then by last name.

I did do a macro that copies sheet1, which has the list of names, into sheet2. Then in sheet two the macro continues to delete all of the names that are not associated with those she inputs into Cells A1 and B1. The problem is that it takes a very long time, since the list of names is very long.

Do you have a macro that can shorten the time element? If not, I'll tell her to bite the bullet and use the Excel auto filter.

[Aj_old]

So, you are talking about a single file, which I suppose, should always have the same structure.
To do a macro one needs to know the range that should be filtered, and another very important aspect, is if she wants the filtered data on the same sheet (to filter in place) or she wants the results on a different sheet for each filter operation. In the second case one needs to know what should be the name of that sheet.

[Aj_old]

By the way, if you can, please upload the file with any sensitive data removed, and replaced with some dummy date. We just need to know the structure of the data.

[PincivMa]

I did a ficticious worksheet with the structure in tact. Sheet1 has the original data while sheet2 has a copy of the original data starting in Cell C1. She puts the names to be filtered in Sheet2 in Cells A1 and B1. Then by running a macro the data in sheet2 gets filtered.

I forgot to put the original data in sheet2 starting in Cell C1. But you get the idea.

[Zack Barresse]

Sheet 2 has nothing in it.

[Aj_old]

Take a look at the attached workbook.

[PincivMa]

Thanks Aj_old. WOW!!! That is some mean programming. I understand some of it but not all of it but it does work. I will try and break down the code line by line to make sense of it. If I get stuck on a line of code, I will get you to explain it to me, if you don't mind.

Thanks again.

Mario

[Aj_old]

You are welcome.
Of course if you have any questions, just ask, I'll be glad to help.

[PincivMa]

Hi Aj_old

I used your filtering macros and they worked real well on the names portion of my workbook. However, in the parts section of another workbook, I'm aving problems. Attached is the parts workbook with your macros. This workbook has 4 worksheets. The third worksheet works like a charm. The fourth worksheet does not work at all. First of all, I get a bug on the line that says "b.cells(1, 3).PasteSpecial xlPaste Value". I then realized that the copy area was from A1 to IV1, so the paste method failed. I then cleared the Receiving sheet from Columns H to Colums IV. When I ran the macro again, I got the message box. But I do know that CT and 0079558 does exist. Can you fix these two errors for me??

Thanks,

Mario

[PincivMa]

I will try to upload the file again!! Hop it works this time!!!