Copy table data to new table using VBA Worksheet Change event

Contributor

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 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 can if it will make this easier. I 

7 Replies

@Edg38426 

 

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.

 

 

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

@Edg38426 

 

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.

@Edg38426 

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".

estimating orders.JPGwork orders.JPG

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?

@Edg38426 

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.

estimating orders.JPGwork orders.JPG

 

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