Automatically adding rows to one table based on cell value of another table

[dallen8028]

I am looking to Automatically add rows to one table based on cell value of another table. Example: If [Table 1 [cell A1]]="Maintenance" then [Table 2] will copy [Table 1 [Cells B1,C1]] to [Table 2[cells F1,G1]] then auto TAB to add a new row to [Table 2] for the next "Maintenance" match. As entries are input to Table 1 that have "Maintenance" in cell A1 the process continues. I would upload the two tables but I do not have upload privileges yet. I apologize for any confusion ahead of time. Please advise. Thanks in advance.

 

[My Aswer Is This]

So you must have these two tables on different sheets.

When I refer to Tables I would say Table1 column 1
Not Table1 A1

And from both of your descriptions both Tables must start in column A of your sheet.

And normally row 1 of your Table would be your Table Header.

Can you clear that up for me please.

Are we sure were talking about Excel Tables or Excel Sheets

 

[My Aswer Is This]

And when I say Column 1

I mean column 1 of the Table not column 1 of the sheet.
A Table can be put anyplace on a sheet so column 1 of the Table may mean Column 14 of the sheet.

 

[dallen8028]

So you must have these two tables on different sheets.

When I refer to Tables I would say Table1 column 1
Not Table1 A1 - You are correct

And from both of your descriptions both Tables must start in column A of your sheet. - No, both tables on same sheet with the headers on the same row.

And normally row 1 of your Table would be your Table Header. - This is correct, migrate all Row 1 to Row 2.

Can you clear that up for me please. - Yes

Are we sure were talking about Excel Tables or Excel Sheets- Excel Tables

Do the answers help?

 

[My Aswer Is This]

Your subject Title:

Automatically adding rows to one table based on cell value of another table​

So are you saying you want this to happen when

Maintenance​

is entered in Table 1 Range A1

So each time you will always be entering the same value into the same cell.

Or do you mean when you enter

Maintenance​

into column 1 of Table1

So first time it would be Table1 Column1 row 1
Next time it would be Table1 Column1 row 2

 

[dallen8028]

Your subject Title:

Automatically adding rows to one table based on cell value of another table​

So are you saying you want this to happen when

Maintenance​

is entered in Table 1 Range A1

So each time you will always be entering the same value into the same cell.

Or do you mean when you enter

Maintenance​

into column 1 of Table1

So first time it would be Table1 Column1 row 1
Next time it would be Table1 Column1 row 2

I was trying to simplify with above. Sorry for the confusion. Here are images of the two tables.

Table One

Travel History
Check Out Date	Chek Out Time	Driver	Event	Start Mileage	Stop Mileage	Total Mileage	Check in Date	Check in Time	Total Time Used
8/7/2018	15:58	Sampuel, Jose	Maintenance		103,805		8/9/2018		#################
8/7/2018				0	103,805	0			#################

<colgroup><col style="mso-width-source:userset;mso-width-alt:3770; width:81pt" width="108" span="4"> <col style="mso-width-source:userset;mso-width-alt:3258; width:70pt" width="93" span="2"> <col style="mso-width-source:userset;mso-width-alt:8308;width:179pt" width="238"> <col style="mso-width-source:userset;mso-width-alt:3258;width:70pt" width="93"> <col style="mso-width-source:userset;mso-width-alt:4538; width:98pt" width="130" span="2"> </colgroup><tbody>
</tbody>

Table Two

Last Service Date	Service Type	Next Service Date	Mileage	Service Cost
8/7/2018
8/7/2018			0	0

<colgroup><col style="mso-width-source:userset;mso-width-alt:3467; width:75pt" width="99" span="3"> <col style="mso-width-source:userset;mso-width-alt:3537;width:76pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:4933;width:106pt" width="141"> </colgroup><tbody>
</tbody>

I want the service date and mileage to be copied from table one to table two any time the event column = maintenance and then I want table two to automatically add a row for the next time table one has a maintenance entry. Does that make better sense?

I didn't think I could copy and paste or I would have done it before. Again sorry for the confusion.

 

[My Aswer Is This]

You can try this:
You really do not need to add a new row to table1
Any time you enter a value in a table row
And then enter a new value in the next row a new row is automatically added.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Now when you enter Maintenance into column 4 of Table1 the script will do what you asked for
Copy parts of this row to Table2

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/10/2018  12:59:40 AM  EDT
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Long
If Not Intersect(Target, [Table1[Column4]]) Is Nothing Then
    If Target.Value = "Maintenance" Then
        ans = [Table2[Column4]].Columns.Rows.Count + 2
        Cells(Target.Row, [Table1[Column1]].Column).Copy Cells(ans, [Table2[Column1]].Column)
        Cells(Target.Row, [Table1[Column5]].Column).Copy Cells(ans, [Table2[Column4]].Column)
    End If
End If
End Sub

 

[My Aswer Is This]

If you really think the script needs to add a new row to Table1 then use this script:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/10/2018  4:45:14 AM  EDT
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Long
If Not Intersect(Target, [Table1[Column4]]) Is Nothing Then
    If Target.Value = "Maintenance" Then
        ans = [Table2[Column4]].Columns.Rows.Count + 2
        Cells(Target.Row, [Table1[Column1]].Column).Copy Cells(ans, [Table2[Column1]].Column)
        Cells(Target.Row, [Table1[Column5]].Column).Copy Cells(ans, [Table2[Column4]].Column)
       ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:=True
      
    End If
End If
End Sub

 

[dallen8028]

If you really think the script needs to add a new row to Table1 then use this script:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/10/2018  4:45:14 AM  EDT
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Long
If Not Intersect(Target, [Table1[Column4]]) Is Nothing Then
    If Target.Value = "Maintenance" Then
        ans = [Table2[Column4]].Columns.Rows.Count + 2
        Cells(Target.Row, [Table1[Column1]].Column).Copy Cells(ans, [Table2[Column1]].Column)
        Cells(Target.Row, [Table1[Column5]].Column).Copy Cells(ans, [Table2[Column4]].Column)
       ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:=True
      
    End If
End If
End Sub

Thank you so much for your hard work. I have not applied your script yet as I do not know how to read script that well and do not know if it adds a row to table one or table two. I do not want to auto add a row to table one but to table two each time column 4 in table one reads Maintenance. Is that possible?

 

[My Aswer Is This]

Use the script in Post 7

It should do what you want.
If not tell me what it did wrong.

You cannot know what it does till you test it.

Try it on a copy of your workbook.