Revise marco so searched output is in new workbook plus search 2 sheets, add headers

Thanks so much for replying. My apologies on missing that step. I've since attached a sample! I really appreciate the help. The more I though it through, I realized it would be more efficient if the macro run as follows:

1. I would like the output to be to a new workbook.


2. The macro as written only searches the sheet "HOTLINE" and returns the rows that have the search criteria "Unqualified Lead" in Column A. I would like for it to search 2 sheets: HOTLINE and WEBINAR for the same search criteria.

3. I would like for the output to be in a new workbook with two sheets: HOTLINE and WEBINAR and use the headers from the original tabs for the outputted rows from each original sheet. Basically its like a summary report of the search criteria only.

THANK YOU SO SO MUCH. I've been banging my head trying to figure it out for days but I'm a fairly beginner user.

Originally Posted by sintek

Hi tseb3...

Welcome to the forum...

In future, always upload a sample file of your actual setup....See Yellow Banner above...
I do not have a sample file of yours...but this will give you an idea...

Please Login or Register  to view this content.

Thanks so much for replying. My apologies on missing that step. I've since attached a sample! I really appreciate the help. The more I though it through, I realized it would be more efficient if the macro run as follows:

1. I would like the output to be to a new workbook.


2. The macro as written only searches the sheet "HOTLINE" and returns the rows that have the search criteria "Unqualified Lead" in Column A. I would like for it to search 2 sheets: HOTLINE and WEBINAR for the same search criteria.

3. I would like for the output to be in a new workbook with two sheets: HOTLINE and WEBINAR and use the headers from the original tabs for the outputted rows from each original sheet. Basically its like a summary report of the search criteria only.

THANK YOU SO SO MUCH. I've been banging my head trying to figure it out for days but I'm a fairly beginner user.

Originally Posted by sintek

Please Login or Register  to view this content.

Sintek- thank you again. When I applied the above code, I get a "AutoFilter method of range class failed" error. I've attached a sample Excel in my original post if that helps.

Understood. Thanks for mentioning it.

How can I edit the code to include the first 2 rows of each sheet in the output? The 2nd row was the column heading and I forgot to mention it.

Sintek - I updated the code and it works perfectly on the sample file. However, when I apply it to my working file, I get "Run-time error '1004': AutoFilter method of Range class failed"

The only difference from the sample file I provided is that my working copy has 10+ sheets and that the two searched sheets "hotline" and "webinar" have data validation in certain columns (choosing from a drop-down list). The column A referenced in the code is a one of those data validation columns. Could that be throwing off the code?

I've attached an updated sample (with your code as well).

Works! Thank you very much again for all your assistance!