Creating a report of expired stock

[denja705]

Good evening all,

New member today looking for help with my excel question.
I have a work book with several tabs with medical supplies. Each page will have NIIN numbers, description of the item, how many, unit of Measure (UOM) and expiry date.
What I am after is that when the items expire or about to expire indicated my conditional formatting, that I run a macro and can spit out the items across the spreadsheets that requires ordering.

I wish the report to have the NIIN, Description, quantity and UOM so that i can just copy and paste it directly into an order form.

I hope this all makes sense. have uploaded a mock workbook so you can see.

Thanks in advance!

[vcoolio]

Hello Denja,

Try the following:-

Please Login or Register  to view this content.

I'm assuming that the ARK 1 sheet is the summary sheet. The code allows for anything expired from the current date and due to expire from the current date plus seven days.

I've attached your sample with the code implemented. Click on the "RUN" button to see how it works. For the sake of the exercise, I've also allowed for the dates to be transferred over as well just to show you more clearly how the code works.

Test it in a copy of your actual workbook first.

I hope that this helps.

Cheerio,
vcoolio.

[denja705]

Hi vcoolio,
Thanks for quick reply.
ARK 1 is another type of medkit rather than a summary sheet. Ideally it inputs it on a new sheet.
I love how it works though!

[vcoolio]

Hello Denja,

Ah, I see. We're on the right track at least.

Create a "Summary" sheet in your workbook then, with the code, change this line:-

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

(or whatever you'd like to call it)

and change this line:-

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

then assign the amended code to the button and all should work as you would like.

I hope that this helps.


Cheerio,
vcoolio.

[denja705]

Hi again vcoolio,

I have tried to do what you said, but in the process have stuffed something up.
I've attached a more accurate representation of my spreadsheet; however on my actual one, i have a fair few more tabs.
Hopefully if i get one tab working, the rest should be easy!

Thanks again for your help!

[vcoolio]

Hello Denja,

If you are getting an "Autofilter" error message in the second sample you have supplied, its because of the empty sheet. Delete the empty sheet.

Next, remove the sort line of code:-

Please Login or Register  to view this content.

It wasn't really necessary.

Hence the amended code should look like this:-

Please Login or Register  to view this content.

Another way around the empty sheet problem (assuming you don't want to delete it) is to add a little more to the code as follows:-

Please Login or Register  to view this content.

The additional bits in red font should prevent any error messages due to empty worksheets.

BTW, the code won't return anything to the "Demand" sheet in the second sample as there aren't any expired dates or shortly to expire dates.

I hope that this helps.

Cheerio,
vcoolio.

[denja705]

Thanks alot! Works just like I want it to! One small glitch is that the macro doesn't want to work on the last 2 sheets even though they are formatted exactly the same.
If i copy an expired item over from another sheet, the macro will work only on that one item and not the rest of the sheet.
Is there an easy fix?

[vcoolio]

I'm not sure what would be causing that.
Could you please upload the sample again in full this time. That'll be the easiest way to try and sort it out for you.

Cheerio,
vcoolio.

[denja705]

Hi Vcoolio,

Here is the entire document. Thanks for looking at it. I cant for the life of me get my head around why is isnt working.

Cheers

[vcoolio]

Hello Denja,

The reason that you're having that problem is because your worksheets are not set out in a uniform manner so the autofilter cannot distinguish where its supposed to be filtering from on the different worksheets.

Autofilter is an important and efficient tool when there are multiple worksheets as source sheets, especially those with large data sets and will make data transfer seamless. However, one needs to work on its terms and it also requires headings to function correctly (one worksheet does not have headings at all). Order is important with data bases.

Can you please re-format your worksheets uniformly with the headings:-

NIIN Drug QTY UOM Expiry Date # Resupply

placed in Row1 of each source worksheet then upload the modified version of your workbook again.

Cheerio,
vcoolio.

[denja705]

Just did those changes and boom it works! Thanks again for your help Vcoolio!

[vcoolio]

You're welcome Denja. I'm glad that I was able to help.

Cheerio,
vcoolio.