CODEWITHSUNDEEP
excelvba
Tam Coton
Tam Coton

Reputation: 864

Construct a pair of mirrored cells in two different worksheets

I'm trying to create a pair of cells on two different sheets of my workbook, such that anything you enter into one cell gets copied across to the other. If this was one way, it would obviously be trivial. I want to make it so that the link is two-way, though. That's going to involve VBA scripting, and I'm something of a novice at that. Does anyone have some pointers on how to learn what I need to know? I have Googled, but that just produced some code samples without explanation, and I don't want to put in code that I don't understand.

Upvotes: 0

Views: 6338

Answers (2)

CLL
CLL

Reputation: 26

Let's assume you have Sheet1 and Sheet2 and their names are exactly that.

  1. At Sheet1, go to Developer and click on View Code. You should be looking at a blank coding sheet for Sheet1
  2. On the left drop down of the coding area, select Worksheet instead of the default (General)
  3. On the right drop down. select Change. You would automatically be given the following:
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Changes to any cell in Sheet1 will trigger the code above.

The cell that has been changed is referred to as the Target range.

You can easily identify the address and content of the Target range using Target.Address and Target.Value. You can then use this address and value to update Sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)

     ThisWorkbook.Sheets("Sheet2").Range(Target.Address).Value = Target.Value

End Sub

To copy data from Sheet2 to Sheet1, just repeat the steps above but at Sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)

     ThisWorkbook.Sheets("Sheet1").Range(Target.Address).Value = Target.Value

End Sub

However, if change to Sheet1 will trigger a change to Sheet2, which in turn will trigger a change to Sheet1, the changes will happen for an indefinite time which does not make sense. Therefore, we need to add some code to apply the change only when the values on both Sheet1 and Sheet2 are different. If the values are the same, there is no need to apply change. Therefore, the code for Sheet2 should appear as below:

Private Sub Worksheet_Change(ByVal Target As Range)

     If ThisWorkbook.Sheets("Sheet1").Range(Target.Address).Value <> Target.Value Then

        ThisWorkbook.Sheets("Sheet1").Range(Target.Address).Value = Target.Value

     End If

End Sub

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

You would use a pair of event macros to accomplish this. Here is an example that you can adapt:

mirror changes

Upvotes: 0

Related Questions