Auto update data between sheets

Closed
dana - May 19, 2016 at 03:39 AM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - May 22, 2016 at 08:48 AM
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

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
May 22, 2016 at 08:48 AM
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.
0