Forum Discussion
Copy table data to new table using VBA Worksheet Change event
If I'm understanding this correctly, you might be able to accomplish your desired end without a VBA macro. My impression is that the difference between your "A" and your "B" sheets is simply that on the "B" list are completed projects while "A" projects are still in process.
IF that's correct, you could just have a master database and, based on entries in one or two of the columns there, be able to display on a "dashboard" sheet either those still in process OR those that are completed, (i.e. the equivalents of your current "A" and "B"). They could be sorted on either sheet in whatever order you wish (e.g., date started, date completed, alphabetical).
Is it possible--without revealing any confidential info--to post a copy of the workbook to OneDrive or GoogleDrive, with access granted via a link posted here? That way, I or somebody else, could demonstrate this for you.
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".