Excel help please

[abdulkharij]

[TABLE="width: 256"]
<TBODY>[TR]
[TD="colspan: 2"]Hi evryone,</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]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>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]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>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name</SPAN>[/TD]
[TD]Hours</SPAN>[/TD]
[TD]Rate</SPAN>[/TD]
[TD]Total</SPAN>[/TD]
[/TR]
[TR]
[TD]jhon</SPAN>[/TD]
[TD]35</SPAN>[/TD]
[TD]10</SPAN>[/TD]
[TD]350</SPAN>[/TD]
[/TR]
[TR]
[TD]David</SPAN>[/TD]
[TD]20</SPAN>[/TD]
[TD]11</SPAN>[/TD]
[TD]220</SPAN>[/TD]
[/TR]
[TR]
[TD]Mark</SPAN>[/TD]
[TD]50</SPAN>[/TD]
[TD]12</SPAN>[/TD]
[TD]600</SPAN>[/TD]
[/TR]
[TR]
[TD]Hellen</SPAN>[/TD]
[TD]40</SPAN>[/TD]
[TD]11</SPAN>[/TD]
[TD]440</SPAN>[/TD]
[/TR]
[TR]
[TD]Iren</SPAN>[/TD]
[TD]40</SPAN>[/TD]
[TD]13</SPAN>[/TD]
[TD]520</SPAN>[/TD]
[/TR]
[TR]
[TD]Nicole</SPAN>[/TD]
[TD]39</SPAN>[/TD]
[TD]14</SPAN>[/TD]
[TD]546</SPAN>[/TD]
[/TR]
[TR]
[TD]Thank you,</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL span=4></COLGROUP>[/TABLE]
​

 

[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