Forum Discussion
Copy table data to new table using VBA Worksheet Change event
Here is the link to the workbook in my Google Drive:
https://drive.google.com/file/d/1A4FD-iCN-bew305POK6GXjha22bzUEJ-/view?usp=share_link
I can certainly upload a copy of these workbooks, but actually I would not describe the two worksheets in that way.
Nor would I describe it that way, now that I've seen what you have.
Basically, each table is a list of work orders used by two different departments. Some of the work order items will need to be sent back and forth between the two tables once completed based on the job type.
And now that I've seen this additional description, as well as having viewed the workbook, I think we need to start over, with a more complete description of, at minimum:
- what each of the three tabs in this workbook represent
- what it is that needs to be sent back and forth between which of the tabs
- how it is determined--the criteria used--that will dictate what goes where
It may well be that VBA is needed (and I'm not versed in that set of tools, but there are others here who are). But whether that is or not, we need to have a clear and unambiguous description of the task at hand.
- 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.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 SubYou 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".
- 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?