Excel help please

[abdulkharij]

Hi evryone,</SPAN>
I have the following table in sheet-1 from an excel workbook. I want an excel formula that can allow me to automatically add a row to similar table in sheet-2 if I add it to the main table in sheet-1.</SPAN>
Example: If I add a row with the name "Jane" in sheet-1 it should automaticaly be added to similar table in sheet-2</SPAN>
Name</SPAN>	Hours</SPAN>	Rate</SPAN>	Total</SPAN>
jhon</SPAN>	35</SPAN>	10</SPAN>	350</SPAN>
David</SPAN>	20</SPAN>	11</SPAN>	220</SPAN>
Mark</SPAN>	50</SPAN>	12</SPAN>	600</SPAN>
Hellen</SPAN>	40</SPAN>	11</SPAN>	440</SPAN>
Iren</SPAN>	40</SPAN>	13</SPAN>	520</SPAN>
Nicole</SPAN>	39</SPAN>	14</SPAN>	546</SPAN>
Thank you,</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=4></COLGROUP>

 

[L. Howard]

Hi abdulharij,

Maybe something like this, goes in the sheet 1 code module.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 4 Then Exit Sub
If Target.Count > 1 Then Exit Sub

Dim cols As Range
Dim colRow As Long
colRow = Target.Row

Set cols = Sheets("Sheet1").Range(Cells(colRow, 1), Cells(colRow, 4))

If Application.WorksheetFunction.CountA(cols) = 4 Then
  cols.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2)
End If
End Sub

 

[L. Howard]

Just to add, should you want values only, and you have formulas on sheet 1.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 4 Then Exit Sub
If Target.Count > 1 Then Exit Sub

Dim cols As Range
Dim colRow As Long
colRow = Target.Row

Set cols = Sheets("Sheet1").Range(Cells(colRow, 1), Cells(colRow, 4))

If Application.WorksheetFunction.CountA(cols) = 4 Then
  cols.Copy
  Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
End If
End Sub