How to update a 2nd Worksheet from another Worksheet

**Spellbound** · 11-11-2008, 07:01 PM

Hi all

Earlier today RoyUK supplied me with this code for updating a worksheet when a cells content changes. This works fine but I now need to have a 2nd worksheet update itself when the cell in another worksheet changes.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
ActiveSheet.Calculate
End Sub

To clarify, I need Worksheet1 to update itself when cell B2 in worksheet2 changes.

TIA ...spellbound

**Leith Ross** · 11-11-2008, 07:42 PM

Hello Spellbound,

What's the sheet name and the cell?

Sincerely,
Leith Ross

**Spellbound** · 11-11-2008, 07:45 PM

Hi Leith

Lets say worksheet1 is called Input and worksheet2 is called Results.

So I need the Input worksheet to update itself when B2 in the Results worksheet changes.

TIA ...spellbound

**Leith Ross** · 11-11-2008, 07:49 PM

Hello Spellbound,

Change the line ActiveSheet.Calculate To Worksheets("Input")

Sincerely,
Leith Ross

**Spellbound** · 11-11-2008, 08:08 PM

Leith

We may be on the wrong track, I inserted the code in the Input worksheet but it did not work.

I am using one of my test workbooks to do some evaluation work and have attached that as it may help.

The code in the Results works fine and updates that worksheet when cell B2 changes. However for error checking purposes, I have linked this worksheet back to the Input worksheet using O2 to link the dates but I now need this worksheet to update itself when B2 in Results changes, to create the cross reference.

TIA ...spellbound

**Leith Ross** · 11-11-2008, 08:23 PM

Hello Spellbound,

Replace the macro on the Results worksheet with the code below, and remove the macro from the Input worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address <> "$B$2" Then Exit Sub
  ActiveSheet.Calculate
  Worksheets("Input").Range("$O$2") = Target
  Worksheets("Input").Calculate
End Sub

Sincerely,
Leith Ross