Auto data move between Tabs

[propolis]

Hi,

From the link to my test spreadsheet, I have 3 tabs, Site 1 and site 2 and site 3

What I am after is that the data of the 3 tabs populate the Main Page tab automatically as I enter data in Site 1 and site 2 and site 3 tabs

What would be nice is if I am on the Main Page tab, and I delete a row, it also deletes it from the relevant row in the relevant Site 1 and site 2 and site 3 tabs

Thank you for any help

Test Book1

 

[My Aswer Is This]

So your wanting to have three exact copies of sheet named "Master"

Is that what you want?

And I never open Files posted on this Forum

 

[propolis]

Hi,

What I am after is that the data from the 3 tabs to automatically populate a Main Page tab as I enter data in the tabs for Site 1 and site 2 and site 3

The only difference between the site tabs is that the first column of each site tqab will have a unique id. All the headings will be the same.

As I finished entering a row, and starting a new one, the main page tab has to be updated already

 

[James006]

Hi,

In order to Combine all your Data from All Sheets to your Main Page sheet ... you can test following :

Sub Combine()
' Combines All Data in All Sheets to Main Page
Dim i As Long
Dim last1 As Long
Dim lasti As Long


    For i = 2 To Sheets.Count
      last1 = Sheets("Main Page").Cells(Application.Rows.Count, "A").End(xlUp).Row + 1
      lasti = Sheets(i).Cells(Application.Rows.Count, "A").End(xlUp).Row + 1
        Sheets(i).Range("A2:D" & lasti).Copy Destination:=Sheets("Main Page").Range("A" & last1)
    Next i
End Sub

Hope this will help

 

[propolis]

Hi,

I have tried this and it does not work very well.

When I have entered data on the Site 1 or Site 2 or Site 3 tab, it does not appear on the Main Page tab. I have to run the macro, but then it loads everything from the Site 1 - 3 tabs and then I have many duplicates on the Main Page tab

 

[James006]

Hello,

If you are after a Fully Automatic Update, could you confirm your manual input in your various tabs always end up with Column D ...(i.e the operating system) ...

 

[propolis]

Hi,

For Site 1 Site 2 and Site 3 tabs, column A will have a unique sequencial number. It wont end on column D, but for this test we can use column D (can change the stop column in the code).

For Site 1 we can have S1, S2, S3 etc. For Site 2 we can have B1, B2, B3 etc and for Site 3 we can have M1, M2 , M3 etc as a unique number in column A1, A2, A3 etc.

So the main page reflects everything thats on the 3 site tabs - when I delete a row on any of the site tabs, it also needs to delete that same row from the main page tab.

When I add a row on any of tha site tabs, and I go to the next row, it needs to add that row I did done automatically to the main page tab.

Hope this helps

 

[James006]

Hi again,

Below is the Event Macro

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
Dim last As Long
last = Sheets("Main Page").Cells(Application.Rows.Count, "A").End(xlUp).Row + 1
If Sh.Name <> "Main Page" Then Sh.Range("A" & Target.Row & ":D" & Target.Row).Copy Destination:=Sheets("Main Page").Range("A" & last)
End Sub

Hope this will help

 

[propolis]

Hi,

Thank you for the quick response. I have tested and all looks OK except 1 items: . . . . when I delete a row on any of the site tabs, it also needs to delete that same row from the main page tab. This does not work

 

[James006]

Glad to hear the Event Macro works as expected ... to Add Records

Regarding the Delete of specific rows... since there is no BeforeDeleteEvent ...

will have to look into this issue ...