Insert a new row in two completely different sheets

[blaisjoel]

Hi, I am trying to add a unit in worksheet (new assets) while having it automatically inserted in the other worksheet (fleet replacement planning) with the unit ID and unit type.


new assets worksheet

unit id	unit type	delivery date	retail cost
lt-16	trailer log	15 dec 16	100000
ct-3	truck tractor	15 dec 16	125000

<tbody>
</tbody>

fleet replacement planning worksheet

unit id	unit type	current hours	rebuilt
trailer log		125
lt-15	trailer log		n
truck tractor
ct-2	truck tractor	1254	n

<tbody>
</tbody>

when lt-16 is added in new assets worksheet, it should also be added to fleet replacement planning worksheet automatically.

 

[cpatel13]

Hi, I am trying to add a unit in worksheet (new assets) while having it automatically inserted in the other worksheet (fleet replacement planning) with the unit ID and unit type.


new assets worksheet

unit id	unit type	delivery date	retail cost
lt-16	trailer log	15 dec 16	100000
ct-3	truck tractor	15 dec 16	125000

<tbody>
</tbody>

fleet replacement planning worksheet

unit id	unit type	current hours	rebuilt
trailer log		125
lt-15	trailer log		n
truck tractor
ct-2	truck tractor	1254	n

<tbody>
</tbody>

when lt-16 is added in new assets worksheet, it should also be added to fleet replacement planning worksheet automatically.

Hie blaisjoel,

Find the Below Code, insert that code in the button once it is done it would give you the change as you want.
Fill all the data, in New asset worksheet and then press the button which would do the process in a blink

Sub Tranferdata()
Dim LastRow As Long
    With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    Range("A" & LastRow).Select
    Range(ActiveCell, ActiveCell.Offset(0, 1)).Copy
    Workbooks.Open ("Full Path of your Fleet Replacement Planning Worksheet")
    With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Range("A" & LastRow + 2).Select
    ActiveCell.PasteSpecial xlPasteValues
    ActiveCell.Offset(0, 1).Copy
    ActiveCell.Offset(-1, 0).Select
    ActiveCell.PasteSpecial xlPasteValues
    ActiveCell.Offset(2, 0).Select
    ActiveCell.Clear
    ActiveWorkbook.Close savechanges:=True
    End With
End Sub

Regards
CPatel13

A Like and feedback would be appreciated