I think this will do what you want.
In the code module of fileB.xls Sheet1
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Call dupRange(Target)
Rem Call dupSheet(Target)
End Sub
In a normal code module:
Sub dupRange(passTarget As Range)
Dim changedRay As Range, changedArea As Range
Set changedRay = Application.Intersect(passTarget.Parent.Range("e:f"), passTarget)
If Not (Nothing Is changedRay) Then
For Each changedArea In changedRay.Areas
Workbooks("fileA.xls").Sheets(changedRay.Parent.Name).Range(changedArea.Address).Value = changedArea.Value
Next changedArea
End If
End Sub
Sub dupSheet(passTarget As Range)
With passTarget.Parent.UsedRange
Workbooks("fileA.xls").Sheets(.Parent.Name).Range(.Address).Value = .Value
End With
End Sub
Any change to Columns E and F of sheet1 of fileB.xls will be reflected in those columns of Sheet1 in FileA.xls
Calling dupSheet instead of dupRange will duplicate all of Sheet1 from fileB to fileA.
Bookmarks