Pull specific data from multiple dynamic worksheets into one report sheet

**valb** · 10-20-2012, 08:45 AM

Hi,
I’m extremely happy I found this forum you would not believe it 
I’ve been struggling with a worksheet for over a month now and been that close to let it go for good.
Long story short I’m trying to help a fried to run the purchasing side of his small family business by setting up an excel workbook.
The way this was supposed to work:
1. For every supplier he needs to keep a separate sheet set as follows:
Order date/ supplier/part no/product description/unit of measure/quantity/price per unit/delivery date/order status
2. Every morning he would need to chase his orders with each supplier individually and mark under the order status header the status for every line in the orders he placed whether the product line was delivered, partially delivered or not yet delivered.
3. Based on the results he would have to resume the chase at a later moment of the day. Meanwhile, further products are being ordered and amendments made on their supplier’s individual sheet.
4. I’ve been trying to figure a way to make it a bit easier as it is awfully time consuming to go through all the supplier sheets and chase the not delivered or partially delivered orders. Therefore I thought I can make it so all the outstanding orders be automatically reported into a separate sheet that he can follow way easier and amend the status according to the chasing results and thus clear up the list as the status changes to delivered.
Obviously non been able to do that so here I am asking you guys to point me into the right direction.
I should mention as well that my level of expertise with excel is close to medium ...no VBA knowledge ... as for macros ... I know what they are 
Cheers
Valb

**mrice** · 10-20-2012, 01:39 PM

You will need to post a copy of the sheet in order to get any reasonable chance of assistance. It would also be very useful if you could indicate what you are expecting the automation to deliver - this is best done with a manually compiled example.

**valb** · 10-21-2012, 03:44 AM

Hi Martin,

Thanks for the reply.
I have attached a sample of the workbook I am talking about with information on how I need it to work.
Only hope I'm making sens

Thanks again for your interest.

**mrice** · 10-21-2012, 03:25 PM

Try pasting the following into the Chasing Report tab in the VBA editor (Alt F11).

Please Login or Register  to view this content.

It reads through the sheets from left to right until it gets to the CHASING REPORT sheet and copies any lines with P/D or N/D to this sheet. The code runs every time the CHASING REPORT sheet is activated and regenerates it completely so it should always be up to date.

**valb** · 10-22-2012, 03:21 AM

It worked like a charm Martin

Much obliged to you, Sir.
I noticed though the status once amended in the CHASING REPORT would not update in the individual sheets but then I figured that this would be impossible as the values pulled by this report are based on the values in the individual sheets and this would probably crate a circular reference with no use for any of us. So the status change would have to be done for each supplier individually. No sweat with that!

Again many thanks for your VBA.

cheers