Forum Discussion
Edg38426
Nov 08, 2022Brass Contributor
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...
Edg38426
Nov 08, 2022Brass Contributor
Thank you for laying that out! I will do my best to better articulate what I'm needing:
The Work Orders sheet is the list of all work orders tasked to Department A. The Estimating Orders is the list of jobs for Department B. The "Data" sheet is simply a dump for data validation lists and other assorted information. It is technically irrelevant to my request. Department A sends jobs over to Department B to QC once they are finished with them, and also vice versa.
What I need is this: When a completed date is entered for a work order on the "Work Orders" sheet (the table name there is "tbldLedger") that has a job type listed as "To Estimating QC", I need everything in Columns C, E, J, and L on that sheet to auto-populate into a new row in the table on the Estimating Orders sheet (table named "EPOLedger6").
One thing is that the table columns on the second sheet are not the same as on the first sheet. I think that it might be easier if they were for some reason, so I can change them to match if that makes this work more smoothly.
The Work Orders sheet is the list of all work orders tasked to Department A. The Estimating Orders is the list of jobs for Department B. The "Data" sheet is simply a dump for data validation lists and other assorted information. It is technically irrelevant to my request. Department A sends jobs over to Department B to QC once they are finished with them, and also vice versa.
What I need is this: When a completed date is entered for a work order on the "Work Orders" sheet (the table name there is "tbldLedger") that has a job type listed as "To Estimating QC", I need everything in Columns C, E, J, and L on that sheet to auto-populate into a new row in the table on the Estimating Orders sheet (table named "EPOLedger6").
One thing is that the table columns on the second sheet are not the same as on the first sheet. I think that it might be easier if they were for some reason, so I can change them to match if that makes this work more smoothly.
OliverScheurich
Nov 08, 2022Gold Contributor
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngBereich As Range
Set rngBereich = Range("B2:B10000")
Application.EnableEvents = False
If Target.Cells.Count > 1 Then GoTo done
If Not Application.Intersect(Target, rngBereich) Is Nothing Then
If IsDate(Target.Value) And Target.Value <> "" And Target.Offset(0, -1).Value = "To Estimating QC" Then
Sheets("Work Orders").Cells(Target.Row, "C").Copy _
Destination:=Sheets("Estimating Orders").Range("C" & Rows.Count).End(xlUp).Offset(1)
Sheets("Work Orders").Cells(Target.Row, "E").Copy _
Destination:=Sheets("Estimating Orders").Range("E" & Rows.Count).End(xlUp).Offset(1)
Sheets("Work Orders").Cells(Target.Row, "J").Copy _
Destination:=Sheets("Estimating Orders").Range("J" & Rows.Count).End(xlUp).Offset(1)
Sheets("Work Orders").Cells(Target.Row, "L").Copy _
Destination:=Sheets("Estimating Orders").Range("L" & Rows.Count).End(xlUp).Offset(1)
Else
End If
End If
done:
Application.EnableEvents = True
Exit Sub
End Sub
You can try this code. If you enter a date in range B2:B10000 and there is "To Estimating QC" in the adjacent cell in column A the data from columns C, E, J, and L in the same row is added to sheet "Estimating Orders".
- Edg38426Nov 09, 2022Brass ContributorThank 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?- OliverScheurichNov 09, 2022Gold Contributor
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 IfEnd 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 IfEnd 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 IfEnd 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 IfEnd If
End Sub