abdulkharij
New Member
Hi evryone,
I have similar tables in sheet1 and sheet2. I want Rows, with new names, to be added automatically to table in sheet2 if I manually add them to sheet1. I have the following formula that helped me to do that, but the problem with it is that when I manually add a Row to sheet1 it goes automatically to the last Row in sheet2. However, I want someone to help me fix this formula so manually added Rows can be automatically added to the same spot in sheet2 when I manually add them to sheet1.
Example: If I add a row with the name "Jane" in sheet-1 under "Davis" it should automatically be added to similar table in sheet-2 under "David"
Sheet1
A B C D
Name Hours Rate Total
jhon 35 10 350
David 20 11 220
Mark 50 12 600
Hellen 40 11 440
Iren 40 13 520
Nicole 39 14 546
Sheet2
A B C D
Name Hours Rate Total
jhon 35 10 350
David 20 11 220
Mark 50 12 600
Hellen 40 11 440
Iren 40 13 520
Nicole 39 14 546
Code:
I have similar tables in sheet1 and sheet2. I want Rows, with new names, to be added automatically to table in sheet2 if I manually add them to sheet1. I have the following formula that helped me to do that, but the problem with it is that when I manually add a Row to sheet1 it goes automatically to the last Row in sheet2. However, I want someone to help me fix this formula so manually added Rows can be automatically added to the same spot in sheet2 when I manually add them to sheet1.
Example: If I add a row with the name "Jane" in sheet-1 under "Davis" it should automatically be added to similar table in sheet-2 under "David"
Sheet1
A B C D
Name Hours Rate Total
jhon 35 10 350
David 20 11 220
Mark 50 12 600
Hellen 40 11 440
Iren 40 13 520
Nicole 39 14 546
Sheet2
A B C D
Name Hours Rate Total
jhon 35 10 350
David 20 11 220
Mark 50 12 600
Hellen 40 11 440
Iren 40 13 520
Nicole 39 14 546
Code:
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If TypeName(Target) = "Range" Then
If LenB(Target.Cells(1, 1).Value) > 0 Then
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.SpecialCells(xlCellTypeLastCell).Row = UsedRange.Rows.Count Then
If UsedRange.Rows.Count > Sheet2.UsedRange.Rows.Count Then
For Each cell In Target.Cells
If Not Intersect(cell, Range("A:A")) Is Nothing Then Sheet2.Cells(Sheet2.UsedRange.Rows.Count + 1, 1).Value = cell.Value
Next
End If
End If
End If
End If
End If
Set cell = Nothing
End Sub
Last edited by a moderator: