I added a field that is paired with fields on both Sheet2 and Sheet3
The aryPair() rows has to be dimensioned to hold the pairs (was 6 not it's 8)
It holds Ranges so aryPair (7,1) and aryPair (7,2) have to be Set,
Set aryPair(7, 1) = ws1.Range("F10")
Set aryPair(7, 2) = ws2.Range("E12")
The pairs can be Set in any order in the aryPair array
Capture.JPG
It's necessary somehow to 'link' pairs of cells between sheets so that they can be updated
The Workbook_SheetChange event uses aryPair() pairs and the sheet that was changed (Sheet1 or Sheet2/Sheet3)) to see if something needs to be updated
I can do some code polishing that might make it a little more straight forward if you want
If you do, then please attach a SMALL realistic sample of real data
Option Explicit
Public aryPair(1 To 8, 1 To 2) As Range ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Public ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Public rName As Range, rTown As Range, rOrder As Range, rPart As Range
'called in Thisworkbook Open
Sub Init()
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")
Set rName = ws1.Range("D3") ' name
Set rTown = ws1.Range("H3") ' town
Set rOrder = ws1.Range("D6") ' order
Set rPart = ws1.Range("H6") ' part
'------------------------------------------------------------ Sheet1 to Sheet2 / Sheet2 to Sheet1
Set aryPair(1, 1) = rName
Set aryPair(1, 2) = ws2.Range("E2")
Set aryPair(2, 1) = rTown
Set aryPair(2, 2) = ws2.Range("E5")
Set aryPair(3, 1) = rOrder
Set aryPair(3, 2) = ws2.Range("I2")
Set aryPair(7, 1) = ws1.Range("F10") ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Set aryPair(7, 2) = ws2.Range("E12") ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'------------------------------------------------------------ Sheet1 to Sheet3 / Sheet3 to Sheet1
Set aryPair(4, 1) = rPart
Set aryPair(4, 2) = ws3.Range("F2")
Set aryPair(5, 1) = rTown
Set aryPair(5, 2) = ws3.Range("F5")
Set aryPair(6, 1) = rOrder
Set aryPair(6, 2) = ws3.Range("F7")
Set aryPair(8, 1) = ws1.Range("F10") ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Set aryPair(8, 2) = ws3.Range("J11") ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Application.EnableEvents = True
End Sub