Comparing and adding cells from different sheets into final sheet

[cahedger]

Hello,

So I have a pretty complex macro I'm trying to design, but my VBA experience isn't quite there yet... any help would be greatly appreciated. My workbook consists of 3+ sheets (sheets will be added/removed as time progresses), Rep1 (and later on Rep2, Rep3, etc), StoreTotals (this is pulled from a system, so it will vary each time it's pulled) ,and Pacing (This is where everything will be totalled). Each sheet will have more or less rows added as things progress (so I'd need the macro to run to the last row of each sheet essentially). I included a "Pacing(Example)" sheet to sortof give an idea of what I'm looking for. Here's what I'm looking for:

- I need the macro to pull the data from the Rep1 sheet (and eventually Rep2, Rep3, etc) into the Pacing sheet in the format provided (maybe include a totals row every time the Order Request name Changes).
- Go through each row on the Pacing sheet, take the Order ID, and add up the Totals and Total Caps from the StoreTotals sheet that have a matching Order ID into the corresponding columns in the Pacing sheet.
- Add the totals up for each Order Request into a gray row... see "Pacing(Example)" sheet
- If the Total Cap is higher than the Total Goal... add "Warning: Total Cap is Higher than Total Goal" in the Errors column
- If the Total Cap is lower than Total Goal add "Warning: Total Caps is Lower than Total Goal"
- If the total Cap matches the Total Goal... do nothing
- If Total is equal to Total Cap... mark green in Notes with "Order fulfilled for Request"
- If Total is lower than Total Cap:
- Divide Total from Total Cap to get Percent Complete (ie if the Total is 162 and the
Total Cap is 208... 162/208 = 78% percent complete)
- Subtract Start Date from End Date to get Total Days... then subtract Start Date
from Todays Date for Days Running)... divide Days Running by Total Days to get
Percentage of Time Order is Running
- If Percent of Time equals Percentage Complete, we're on Pacing. If Percentage
Complete is greater than Percentage of Time than we're ahead of Pacing. If
Percentage is less than Percentage of Time than we're behind on Pacing
- If on Pacing mark Green with "Pacing" in Notes... if above Pacing mark Green with "Ahead of Pacing" in Notes... if below Pacing Mark Red with "Behind on Pacing" in Notes

... I hope this helps, I'm working on this as I go through Tutorials in the mean time... but I was trying to see if somebody could either figure this out faster, get me a good starting point, or point me in the right direction. I know this sheet is kindof a mess and confusing so if there are any questions please let me know and I'll explain. If this is too large a task, feel free to tell me to piss off and I'll figure it out haha. Thanks tremendously for any help in the mean time.

 

[NARAYANK991]

Hi ,

If you break your requirement down , it is not so complex , since the macro part is a very small one ; all your other requirements can be implemented using formulae.

1. Just transfer data from all sheets which are like "Rep*" ; what this will do is for each sheet in the workbook , whose name is Rep1 , Rep2 , Rep3 ,... , it will transfer data from that worksheet into the Pacing tab ; you need to look for the first blank row in the Pacing tab to transfer the data to.

2. Once all of the data is transferred , insert a blank row every time the Vendor ID changes , and color it grey.

3. Once this is done , the rest can be done by using the SUMIF function in columns H and I , as in :

=SUMIF(StoreTotals!$A$2:$A$13,$G2,StoreTotals!$G$2:$G$13)

=SUMIF(StoreTotals!$A$2:$A$13,$G2,StoreTotals!$H$2:$H$13)

As far as the Notes and Errors columns are concerned , only some simple calculations and IF statements will suffice. Coloring the same can be done using Conditional Formatting.

Narayan

 

[Surendran Setty]

Hi,

Since Rep and StoreTotals sheets have well defined relations (VID and Order ID), I thought I would have more flexibility if ADO recordsets are used. To use ADO, we have to set a reference to Microsoft ActiveX Objects 2.8 library in VBE Editor.

In the attached file, I have fully automated your requirement using ADO. Since you have given only one sample Rep sheet, for testing purpose, I have created a second Rep sheet with the same data as in Rep1 sheet.

To run the macro, please use the sheet Main. You have option to choose either append data or delete old data in Pacing sheet before running the macro.

The macro would work fine if data in both Rep and StoreTotals sheets start from A1 and first row should be the header row.

Regards,
Surendran