I'm assuming that you want to treat each sheet individually meaning that, regardless of which sheet (1, 2 or 3) that you are working on, you would like newly entered data to be immediately transferred to sheet4 (your Summary sheet?) once you enter each row of data.
So, perhaps the following code, placed in the sheet module of each sheet (1,2 & 3), will do as you require:-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim lCol As Long
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
If Target.Count > 1 Then Exit Sub
If Not Application.Intersect(Target, Range("H:H")) Is Nothing Then
If Target <> "" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, lCol)).Copy Sheet4.Range("A" & Rows.Count).End(3)(2)
Application.CutCopyMode = False
Application.ScreenUpdating = True
The code will also delete the row of data that has been transferred from the active sheet to sheet4.
In the above code, you will need to make Column H the last column into which you enter data otherwise the code will not work. If Column H is not your last data input column, then change the column reference (Range("H:H")) in line 9 of the above code to suit yourself.
Following is the link to my test work book which will show you how the above code works:-
In any of the sheets 1,2 or 3, enter any value in any cell in Column H then click away (or press enter or down key) and that row of data will be transferred to sheet 4.
To implement the code into your work book:-
- Right click on the sheet1 tab.
- Select "view code" from the menu that appears.
- Paste the above code in the big white field that then appears.
Repeat the above steps for sheets 2 & 3.
- Test the code in a copy of your work book first.
I hope that this helps.