尝试将代码放在Workbook模块中的Worbook_SheetChange事件中,而不是使用工作表事件:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim wb1 As Workbook
Dim ws1 As Worksheet
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set wb1 = ThisWorkbook
Set ws1 = Sh
If Target.Cells.Count > 1 Then GoTo LetsContinue
'Change cell value on all sheets except for sheet "lists"
If Not Intersect(Target, ws1.Range("D4")) Is Nothing Then
For Each ws In wb1.Worksheets
If ws.Name <> "Lists" Then
If Target.Value <> ws.Range(Target.Address).Value Then
ws.Range(Target.Address).Value = Target.Value
End If
End If
Next ws
Else
GoTo LetsContinue
End If
LetsContinue:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
像这样,您的代码将适用于任何工作簿表 .