multiple search criteria to find and copy from sheet1 and paste into sheet2

[lothario]

Hi,

I have:

Workbook called "Articles".
=> It contains sheets "super_sheet" and "sub_sheet".
=> "super_sheet" contains 20,000 rows of data.
=> "super_sheet" contains the columns "Type", "From Year", "To Year",
"Number", "Description", etc ...
=> Where "Type" is column B, "From Year" is C, "To Year" is D,
"Number" is G, "Description" is H


I would like to:

* Have a button in called "Find Articles" in "sub_sheet".

* This button should take the search criteria from cells "b3", "b4",
"b5", "b6" in "sub_sheet".

* Where cells "b3", "b4", "b5", "b6" in "sub_sheet" are be
respectively labeled "Type", "Year", "Number" and "Description".

* Where "b3", "b5" and "b6" may each contain may text. Or they may be
blank.

* Where "b4" may contain a two digit number with no decimals. Or it
may be blank.

* So when the "Find Articles" button is clicked then the VBA code
would find the rows in "super_sheet" where:
the super_sheet:column B contains the text found in sub_sheet:b3
AND super_sheet:column C has the number greater than or equal to
found in sub_sheet:b4
AND super_sheet:column D has the number less than or equal to
found in sub_sheet:b4
AND super_sheet:column G contains the text found in sub_sheet:b5
AND super_sheet:column H contains the text found in sub_sheet:b6

* Note: in the case of columns B, G and H the text should just contain
the criteria text. There is no need for an exact match or case
sensitivity.

* All rows in "super_sheet" that meet the above 5 conditions should be
copied and pasted into "sub_sheet" starting at cell e10.

* If there are any blanks in "sub_sheet" cells "b3", "b4", "b5", "b6"
then the respective condition does not apply and the remaining
contitions apply.

* If there are all blanks in "sub_sheet" cells "b3", "b4", "b5", "b6"
then no conditions apply so do nothing.

Can you please give me the VBA code for this "Find Articles" button?


Thanks,
Luther

 

[Patrick Molloy]

start macro recorder. set an autofilter on the
super_sheet and then add filters as you describe. Stop
the recording and examine the code.

Your own VBA should
(1) set the filter
(2) set the criteria
(3) copy the resultant visible rows
(3) remove the filter

HTH
Patrick Molloy
Microsoft Excel MVP

 

[lothario]

Hi Patrick,

I tried your suggestion in super_sheet.
In the AutoFilter dialogbox, I specified contains and =sub_sheet2!$B$3
This does not work. None of the rows are selected in super_sheet.
The =sub_sheet2!$B$3 reference does not work.
What am I doing wrong?

On the other hand when I specify contains and rro
I get the correct results because all the rows in that column tha
contain the text rro are displayed.

But I cannot use a hard value like rro.
I need to use the cell reference.
Please tell me how to correctly enter the =sub_sheet2!$B$3 cel
reference in the AutoFilter dialogbox.

Thanks.
Luthe