Nov 08 2022 07:40 AM
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
Nov 08 2022 09:02 AM
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.
Nov 08 2022 09:14 AM
Nov 08 2022 12:19 PM
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:
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.
Nov 08 2022 12:32 PM
Nov 08 2022 02:16 PM
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".
Nov 09 2022 05:20 AM
Nov 09 2022 09:02 AM
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 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