Copy table data to new table using VBA Worksheet Change event

Question

Hello gurus,&nbsp;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 table in "B" when a completed date is entered. I understand Worksheet Change events, but I do not know how to grab specific column data and automatically enter it into the other table. The columns/headers in the two tables do not match, but they&nbsp;can&nbsp;if it will make this easier. I&nbsp;

mathetes · Answer

Edg38426&nbsp;&nbsp;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.&nbsp;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).&nbsp;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.&nbsp;&nbsp;

oliverscheurich · Answer

Edg38426&nbsp;Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngBereich As Range

Set rngBereich = Range("B2:B10000")
Application.EnableEvents = False

If Target.Cells.Count &gt; 1 Then GoTo done

If Not Application.Intersect(Target, rngBereich) Is Nothing Then

If IsDate(Target.Value) And Target.Value &lt;&gt; "" And Target.Offset(0, -1).Value = "To Estimating QC" Then

Sheets("Work Orders").Cells(Target.Row, "C").Copy _
Destination:=Sheets("Estimating Orders").Range("C" &amp; Rows.Count).End(xlUp).Offset(1)
Sheets("Work Orders").Cells(Target.Row, "E").Copy _
Destination:=Sheets("Estimating Orders").Range("E" &amp; Rows.Count).End(xlUp).Offset(1)
Sheets("Work Orders").Cells(Target.Row, "J").Copy _
Destination:=Sheets("Estimating Orders").Range("J" &amp; Rows.Count).End(xlUp).Offset(1)
Sheets("Work Orders").Cells(Target.Row, "L").Copy _
Destination:=Sheets("Estimating Orders").Range("L" &amp; 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&nbsp;"To Estimating QC" in the adjacent cell in column A the data from columns&nbsp;C, E, J, and L in the same row is added to sheet "Estimating Orders".

edg38426 · Answer

I can certainly upload a copy of these workbooks, but actually I would not describe the two worksheets in that way. 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.

Here is the link to the workbook in my Google Drive:
https://drive.google.com/file/d/1A4FD-iCN-bew305POK6GXjha22bzUEJ-/view?usp=share_link

mathetes · Answer

Edg38426&nbsp;&nbsp;I can certainly upload a copy of these workbooks, but actually I would not describe the two worksheets in that way.&nbsp;Nor would I describe it that way, now that I've seen what you have.&nbsp;&nbsp;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.&nbsp;And now that I've seen this additional description, as well as having viewed the workbook, I think we need to start over,&nbsp;with a more complete description of, at minimum:what each of the three tabs in this workbook representwhat it is that needs to be sent back and forth between which of the tabshow it is determined--the criteria used--that will dictate what goes where&nbsp;It may well be that VBA is needed (and I'm not versed in that set of tools, but there are&nbsp;others here who are). But whether that is or not, we need to have a clear and unambiguous description of the task at hand.

edg38426 · Answer

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.

Forum Discussion

Copy table data to new table using VBA Worksheet Change event

Share

Resources