How do I create a report based off specific data requirements?

Im currently using the attached sheet to keep track of our patients paperwork, and insurance. What I need to do is be able to run 2 separate reports. The first would verify insurance and in the case of NONE pull the data of the patients name/DOB/phone number/ and Case manager to a separate sheet for all 26 sheets of data. The second would need to check the paperwork for the words EXPIRED and in that case pull up the data on a separate sheet as well. If possible I would like to make this a separate workbook that can pull the information in.

First I ran the code below, to get all data on 1 sheet.

After that I used a filter on column H.

See the attached file.

Please reply.

Please Login or Register  to view this content.

This is pretty much exactly what I need, just one problem how would I run this from another workbook, and also how do I run this after the workbook has been updated? Sorry I have never touched VBA macro's

Also what would I need to change to create another one that will compile all the Info from the columns A-E in the info in Column B was set to NONE

Respond to #3.

Put the macro in the personal.xlsb

Then you be able to select the macro in all active workbooks

Respond to #4.

just filter on column B.

Thanks a ton for the help +1

ok 1 more question on it, when I run the macro It consolidates the info onto one sheet but stops there. I don't get those nifty drop down sort boxes like you had and its pulling all data not just the expired/None am I doing something wrong?

No, it takes all data off all the sheets in the consolidated sheet.

After that i put an filter on the data.

Excel 2007 => data => filter

thanks for the clarification