Auto update data between sheets [Closed]

Report
-
Posts
1291
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 5, 2021
-
Hello,
I have 3 sheets with data table all linked to sheet #4.
how to set that every time a new data is entered on one of the 3 sheets it will appear on Sheet #4 as well at the bottom of the table?
Thanks!

1 reply

Posts
1291
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 5, 2021
229
Hello Dana,

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)
        Target.EntireRow.Delete
        End If
  End If
  
Sheet4.Columns.AutoFit
Sheet4.Select

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


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:-

https://www.dropbox.com/s/jkfunqrooisks21/Dana%28Worksheet_Change%20event%20in%20each%20sheet%29.xlsm?dl=0

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.

Cheerio,
vcoolio.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!