Generate a pending list from multiple sheets

[stevense28]

Hi there,

I'm really stuck with what I'm trying to do and I'm not even sure if it's possible.

In my line of work I send off samples to other labs for testing. These are logged onto a spreadsheet and the empty fields are filled once results are received. In my example spreadsheet, Column N is blank whenever a result has yet to be received and should therefore appear on the newly generated pending list sheet. I can get a list generated from one sheet but not multiple sheets.

If this can be done then great! But it would be ideal if I could take this one step further. The example spreadsheet given would be for 2014. Would it be possible to have the previous year's workbook (2013) scanned also for pending requests and added to the 'Pending' sheet list? This list should ideally be displayed in date order with the oldest pending request first.

Any help would be much appreciated,

Steven

[sktneer]

Try this code......For detail pls see the attached sheet.

Please Login or Register  to view this content.

[JBeaucaire]

The best way to grab data from multiple sheets in my opinion is a macro, VBA.

I've installed this macro into the PENDING sheet:

Please Login or Register  to view this content.

This will apply an Autofilter to each sheeet in the workbook (Except PENDING) for blanks in column N and copy those rows to the PENDING sheet. THis is an EVENT macro, it occurs each time the PENDING sheet is brought up onscreen, so it will always show the most current data.

[stevense28]

Hi sktneer,

This works very well but I prefer having the pending sheet update automatically when opened rather than having to press a button.

Many thanks for your help though!

Steven

[stevense28]

The best way to grab data from multiple sheets in my opinion is a macro, VBA.

I've installed this macro into the PENDING sheet:

Please Login or Register  to view this content.

This will apply an Autofilter to each sheeet in the workbook (Except PENDING) for blanks in column N and copy those rows to the PENDING sheet. THis is an EVENT macro, it occurs each time the PENDING sheet is brought up onscreen, so it will always show the most current data.

Hi J Beaucaire,

Thank you so much, this is exactly what I was hoping for!

Just one thing though. I may have other sheets within the workbook that don't require filtering. How would I modify the code to take this into consideration?

Regards,

Steven

[sktneer]

Please find the attached sheet to see if this works as per your requirement.

[stevense28]

Please find the attached sheet to see if this works as per your requirement.

Thanks again sktneer and yes, this now works perfectly.

This workbook may ultimately have other sheets that do not require filtering. So, for example, if I had another sheet named 'TOTALS' and this does not require filtering, how can this code be tweaked to take that into account?

Kind regards,

Steven

[JBeaucaire]

For mine, if it's only a few sheets, one way is to exclude them by sheet names:

Please Login or Register  to view this content.

You can add more of those exclusions is you wish.


To access the code in the workbook, right-click on the PENDING tab name and select VIEW CODE.

[sktneer]

You have to include the code in red color to achieve that.....

Please Login or Register  to view this content.

[stevense28]

For mine, if it's only a few sheets, one way is to exclude them by sheet names:

Please Login or Register  to view this content.

You can add more of those exclusions is you wish.


To access the code in the workbook, right-click on the PENDING tab name and select VIEW CODE.

It would never be more than a few sheet names so this is ideal for my purposes.

Many thanks for your help. This will save me a great deal of time at work!

Take care,

Steven

[stevense28]

You have to include the code in red color to achieve that.....

Please Login or Register  to view this content.

Yes, I see that. You've been a great help.

Thanks for your time,

Steven

[sktneer]

Glad to help you. Thanks for the feedback.
If that takes care of your question, please mark your thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.