Automatically Copy Range To Another Sheet Up On Change In Value In Adjacent Column

[Excel Fox]

So 'Target' is the range that just had a value change in the sheet. This can be one or many cells depending on what changes or how it changes. Checking whether it is column 7, is just to ensure that the code only runs if the change of value happened in column G. If that is true, then we check whether the value in cell is 'Yes'. The UCASE is just to work around Excel's default text comparison mode, which is Binary mode. So basically, Yes is not equal to YES. To counter that, yes force the text to be compared as an UPPER CASE text.

Now's the real deal.

Sheets("INITIATING DEVICES").Cells(Target.Row, 1).Resize(, 3).Value

Target.Row will give the row number of the cell that just got changed. And therefore, Cells(Target.Row,1) means some cell in Column A and in the corresponding row of the target cell. Let's say it's Range A2. Resize simply a method for resizing the range from an originating Range. So Resize(X,y) means that the existing range will be resized X rows and Y columns. So in the above case, the range A2 will now be A2:C2, ie, resized 0 rows, and 3 columns.

You know what

 
Sheets("MESSAGE CHANGES").Cells(Rows.Count, 1).End(xlUp)

does. Adding a (1) to any cell reference would simply mean that we are referring to the same cell. However, (2) means we are referring to a range that is +1 offset than the same cell. So if you for example write Range("A1")(1).value, it is the same as Range("A1").Value. However, Range("A1")(2).value is actually equalent to Range("A2").Value, similarly Range("A1")(4) means Range("A4")

So now,

Sheets("MESSAGE CHANGES").Cells(Rows.Count, 1).End(xlUp)

gives the last empty cell of that column, and the (2) makes it the range below that last empty cell. Now we resize this also to 3 columns, and we just write, for example,

Range("A2:C2").value = OtherSheet.Range("A5:C5").value