Auto data move between Tabs

propolis

New Member
Joined
Mar 22, 2019
Messages
32
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

:)
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Last edited:
Upvote 0
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
 
Upvote 0
Hi,

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

Code:
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
 
Upvote 0
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
 
Upvote 0
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) ...
 
Upvote 0
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
 
Upvote 0
Hi again,

Below is the Event Macro

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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 ...
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top