Forum Discussion
Copy table data to new table using VBA Worksheet Change event
Also, one more potential complication - and this may be my ignorance showing - but I already have another sub running in this sheet for an automated email to generate when a completed date is entered. Do I need to run this script in a module, or can you have two subs in the same place?
In the attached file in the "Work Orders" sheet i've converted the table into a dynamic table and the Worksheet_Change event copies the data to the "Estimating Orders" sheet as intended.
If you have two Worksheet_Change events in the same sheet you can combine them into one for example:
This
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngBereich As Range
Set rngBereich = Range("A2:A25")
If Not Application.Intersect(Target, rngBereich) Is Nothing Then
If (Target.Value <= 100) And (Target.Value > 0) Then
Target.Offset(0, 1).Value = Date
Else
End If
End If
End Sub
and this
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngBereich As Range
Set rngBereich = Range("B2:B2500")
If Not Application.Intersect(Target, rngBereich) Is Nothing Then
If (Target.Value > 0) Then
Target.Offset(0, 1).Value = "Everything as expected"
Else
End If
End If
End Sub
can be combined into:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngBereich As Range
Set rngBereich = Range("A2:A25")
If Not Application.Intersect(Target, rngBereich) Is Nothing Then
If (Target.Value <= 100) And (Target.Value > 0) Then
Target.Offset(0, 1).Value = Date
Else
End If
End If
Set rngBereich = Range("B2:B2500")
If Not Application.Intersect(Target, rngBereich) Is Nothing Then
If (Target.Value > 0) Then
Target.Offset(0, 1).Value = "Everything as expected"
Else
End If
End If
End Sub