Forum Discussion
Copy table data to new table using VBA Worksheet Change event
Hello gurus, I have two worksheets with tables in the same workbook ("A" and "B"). I have a column ("Job Type") in the table in "A" that states if the table row will need to be transferred to the...
Thank you for your work on this! My data isn't laid out quite like this, but I tried to adapt it to work for my sheet. I changed the range from "B2:B1000" to "T8:T1000", where the completed date will be entered, and changed the offset position to 0,-17 where the "To Estimating QC" would be. I would think that would work, but I'm not seeing the data populate anywhere on the "Estimating Orders" sheet. Does it matter that the data on that sheet is in a table? The table name is "EPOLedger6", and the destination column names are the same as the table on the "Work Orders" sheet ("tblLedger").
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?
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