VBA - Copy multiple columns using index/match to fit multiple criteria

Question

I'm pretty new to VBA.

I have two sheets. The first is an old large sheet with outdated information and the second contains a subset of the first one with updated data.

Here is a (truncated) example of each sheet.

**Old Large Sheet**

Year Month Region Data1 Data2 CalculatedData1 CalculatedData2
2016 1     1      X1    Y1    X1              X1/Y1
2016 2     1      X2    Y2    X1+X2           X2/Y2
2016 3     1      X3    Y3    X1+X2+X3        X3/Y3
...
2017 1     1      X13   Y13   X13             X13/Y13
...
2025 12    30     XXX   YYY   ...

And the new sheet.

**New Sheet**
Year Month Region Data1 Data2
2017 1     1      X13'  Y13'
2017 2     1      X14'  Y13'
2017 3     1      X15'  Y15'
2017 4     1      X16'  Y16'

Basically, I would like to replace "Data" columns in the old sheet with values from the new sheet based on an index/match of the three first columns.

In Excel, this would mean filtering for a specific period/region and writing an array formula in Data a formula which would be like

{=INDEX('NewSheet'!D2:D1000;MATCH($A2&$B2&$C2;_
             'NewSheet'!$A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000;0))}

And copying it for each "Data" column and each filtered row.

My question is : how can I do this replacement in VBA in a pretty automated way ?

Vicente Galle Vicente Galle · Accepted Answer · 2017-04-25T13:54:51

Hm, do you know about vba triggers or whatever they're called? Like Sub Worksheet_Change for example. Basically a macro will run everytime a cell is changed.

So I'm thinking on something like this:

Sub Worksheet_Change()

   'check cell index
   'check if data is outdated
   'if yes, replace data
   'grab a beer while you revel in a world of automation

end Sub

Source:

https://support.microsoft.com/en-us/help/213612/how-to-run-a-macro-when-certain-cells-change-in-excel

VBA - Copy multiple columns using index/match to fit multiple criteria

1 Answers