If that describes what you want, try putting this in the ThisWorkbook code module
Public KeyRow As String
Public keySheet As Worksheet
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not keySheet Is Nothing Then
If (Sh.Name = keySheet.Name) Or (Sh.Name = "otherSheet") Then
If Target.Cells.Count = 1 Then
If Not Application.Intersect(Target, Sh.Range(KeyRow)) Is Nothing Then
Application.EnableEvents = False
keySheet.Range(Target.Address).Value = Target.Value
Me.Sheets("otherSheet").Range(Target.Address).Value = Target.Value
Application.EnableEvents = True
End If
End If
End If
End If
Set keySheet = Nothing
KeyRow = vbNullString
End Sub
And then assiging this
Sub test()
Dim rNumber As Long
rNumber = Application.InputBox("where the row", Type:=1)
If rNumber < 1 Then Exit Sub
ActiveSheet.Rows(rNumber).Insert
ThisWorkbook.Sheets("otherSheet").Rows(rNumber).Insert
With ActiveSheet.Rows(rNumber)
ThisWorkbook.KeyRow = .Address
Set ThisWorkbook.keySheet = .Parent
.Cells(1, 1).Select
End With
End Sub
Bookmarks