Replace Code to improve processing speed for copy & paste

[paula.mccall]

Hello,

I have attached an example workbook (Data Test) that I have used "index/match" coding in the multiple columns (C - K) (If code exists in cell, cell is highlighted light green). The worksheet is processing updates extremely slow, which I believe may be due to the multiple codes written.

My current "order of operation" is:

1. Pull a data report from an internal data management system each month
2. Paste the data to the next available row in the "Data Test" wb, "SI Data" ws (columns Q:AD)
3. Update the Location and Completion Date List FROM the "Monitoring Reporting Test" wb, "Master" ws TO the "Data Test" wb, "SI Monitor" ws.
4. After the information is updated in the SI Data worksheet (Columns C to K), using the many, many "index/match codes, I will then upload that data back to the Monitoring Report workbook, SI Monitoring tab.


Concern is that problems will only become worse:
These two workbooks will become much larger, as they will continue to update each month for the next 12 months, so I am concerned it will become larger than my memory capacity and/or it will run so slow, it will produce an error and not work at all.

The need:
IF the Location List in the Data Test wb - SI Data ws, column U, matches the Location in the Monitoring Report wb - SI Monitor ws, column A
AND,
the number in the Monitoring Report wb - SI Monitor ws, row 6, columns C, F, I match the numbers in the Data Test wb - SI Data ws, column AE, it would return the values that match from columns AA & AC . (To the SI Monitor ws, From the SI Data ws)
The dates from the SI Data ws, column AA would go to the SI Monitor ws, columns E, H, K
AND,
The values from the SI Data ws, column AC, would go to the SI Monitor ws, columns C, F, I.

Also would like to:
-Copy/Paste from the Data Test to the Monitoring Report without having to open the Monitoring Report
-Do not overwrite any of the existing data in the Monitoring Report
-Paste as values, not formulas

I know this is a "tall order." I have tried multiple vba codes, but each codes seems to provide a new problem (trial codes are available to view in the two attached workbooks).

Any help would be so greatly appreciated.


Kindest Regards,
Paula

[BanginMyHeadOnMyDesk]

Without even started looking at rewriting your VBA, the first thing that I see is you're using array formulas on entire columns. That will certainly slow things down.
Have you tried limiting the formulas to just the required rows? For example, the below formula would replace your current formula in C2 on SI Data:

=IFERROR(INDEX($AC$2:$AC$34,MATCH(1,($A2=$U$2:$U$34)*(C$1=$AE$2:$AE$34),0)),"")

Give that a try first and see if it's still very slow and if so I'll take a look at rewriting the VBA for you.

Beth.

[paula.mccall]

Hello Beth,

First, thank you for the quick response.
I can limit the formulas, which does help to speed things up quite a bit, but the document I provided was only an example. The actual document will have anywhere from 3000 to 4000 entries for each row, so limiting the formula would only be a very temporary fix .

Kindest Regards,
Paula