VBA Code to return a summary sheet of due actions

**ambquinn** · 06-12-2011, 12:37 PM

I'd really appreciate some help on this one as I'm totally confused. I am using Microsot Excel 2003. The idea is fairly simple I have a large workbook, one spreadsheet of which (called review dates) is of client names with corresponding review dates (2 weekly, 3/6/12/18 monthly etc, etc). The columns are conditionally formatted to highlight any dates that fall within a week of the present date but its very time consuming and errror prone to scroll through hundreds of rows of client names plus across a number of columns looking for any due reviews. The idea is then to have a single summary worksheet report, to return all due actions detailing clients name, the date that a review is due and the type of review due, (eg 3 month). As stated any help at all would be good, I'm very new to VBA. Many thanks

A

**jeffreybrown** · 06-12-2011, 02:19 PM

Hi A & Welcome to the Board,

My initial impression is a pivot-table. If this does not suit your needs then how about attaching a small sample workbook of what you have and what you desire.

http://www.pivot-table.com/
http://peltiertech.com/Excel/Pivots/pivottables.htm

**ambquinn** · 06-12-2011, 03:01 PM

Thanks for getting back to me. No, don't think a pivot table would suit the purpose. I have attached a small sample of what I'm looking for, (the actual one is a lot bigger with many more sheets). But essentially, its to return a "table" on the "summary actions due" spreadsheet detailing the client name, date action due and the type of review due, which is obviously based on data on the "review dates" sheet which is a dynamic table hundreds of rows long and may be up to 20 columns across.

**jeffreybrown** · 06-12-2011, 03:26 PM

Ok based on your sample, you only have one name, Callum. Any reason why only one name?

So in the end, do you want all names which are 7 days on either side of today's date along with the type of review?

Do you want the results wiped out everytime you rerun this macro? I don't see a formula that will do it, but that could just be my lack of understanding.

**ambquinn** · 06-12-2011, 03:42 PM

No, sorry I didn't make myself clear enough - I only put the one name (Callum) on the "summary actions due" sheet to show the kind of info that I would need returned from the "review dates" sheet ie the name, date and type of review, but it would need to return all those who fall preferably within 7 days either side of todays date or at least the 7 days prior to today. The macro would be ran once a week in an effort to get a comprehensive list of all due reviews that had to be booked in.
Thanks
A

**TMS** · 06-12-2011, 06:21 PM

I think this will do it:

Please Login or Register  to view this content.

Regards

**jeffreybrown** · 06-12-2011, 09:08 PM

Thank you TMShucks for the assist.

@A: Please let us know if this meets your needs

**ambquinn** · 06-13-2011, 04:12 AM

That is absolutely brilliant! Works a treat many many thanks to both - it was completely doing my head in!

**TMS** · 06-13-2011, 04:15 AM

You're welcome. Thanks for the rep.

If this answers your question, please mark your thread as solved. See my signature for details or the FAQ.

Regards