Duplicate data from on tab to a master tab

[Closed]
Report
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hello,

I need to create a work book wit 5 tabs?

The 1st tab will be a Master Page and the 2nd,3rd and 4th will be data inputted by other users, I would like to be able to see everybody's data inputted automatically copied to the master as the input it so i can see there work load and they still have there own work to page is this possible and how can i create it.

Please can anybody help me.?

Regards

Paul

2 replies

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Paul,

When you open the file Excel could check which row is last used (for multiple sheets). Then when you close the file, Excel could check again for the last row used and paste the difference in rows to the master sheet.

Are people filling in rows of data (1 at the time and below each other) or are they filling cells all over the sheet?

Best regards,
Trowa
Hi TrowaD,

Thank you for commenting yes people will be filling in rows of data (1 at the time and below each other) what I'm trying to do is get a workbook that automatically updates as people input data on it, so that myself and others members of staff can see who is busy and what work is being done.

That's if I can get something like this to work.

Regards

Paul
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
If one delete a row from one of "non-master" sheets, what should happen to the row that was copied to master ?

if one edits a row in one of the "non-master" sheet, what should happen to that row in the master sheet

If objective of this master is to see whats happening in all other sheets without going sheet to sheet, one option that you may want to consider is have a macro, where you ask for copy all data from all sheet for a given time frame. Of course this all depends on what is the purpose and usage of Master
Hi Rizvisa1,

What im after is a workbook that when i enter data one one tab it duplicates it on the MASTER page and when the jobs completed and a date is inputted into the date completed columm it moves it to a tab called Archieve so that i can back track work completed.

So your comment about a Macro is exactly what im after but i need it to work over say 6 tabs for usernames a tab for MASTER and a tab for Achieve so say 8 tabs. Do you think its possible to do this and how can i do it, Im not great on Excel and wish to create this to make my dept more efficent.

I have a excel file i have created if you wish to see what the contents of the columm are if you would like to veiw it.

The main thing aswell is that data isnt deleted from the workbook but just moved to Archieve.

I hope you can help me?

Regards

Paul
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It seems to me that data is not deleted but is and can be edited, which throws a wrinkle that one has to examine if a new row was added or edited.

From what I understood, so far

1. Master sheet contain copied data from all sheets that are still in progress (not ready to be archived). The original sheets still has data.

2. Archive sheet contain moved data from all sheets that were completed (as indicated by a certain column that date). The original sheets will no longer have those rows

If that is the case, most safe way would be.
1 Have a macro to copy data from all sheets into Master
2. Have a macro to move data from all sheets into Archive

OR
3. Combine functionality #1 and functionality #2 into one macro

when you run functionality #1, it deleted previous master data and copies the new data

when you run functionality #2, it moves the data from sheets to archive by appending to what ever is there already

So rather doing it automatically, you run the macro manually to get the results you seek
Hi Rizvisa1

I have created the workbook and worked out the functions for the staff tabs and created a function that when they input a completed date it moves the row from there tab and moves it over to the archive, the only thing i cant seem to do is create a rule to duplicate there work over to the master tab which will hold everyone's work load?

I am sure your right i need a macro to do this but i im not sure how to do this can you advise me please.

I could always send you the file to show you what im trying to create?

Regards

Paul
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I think I have not been able to highlight a issue
Lets say your sheet has 5 columns

When is the right time to move the data to Master sheet. Lets say you say when all 5 columns are filled. This means that as soon as all columns has some data (right or wrong or incomplete or complete) the data will move copied. Now lets say you edit one of the cell or clear out the cell. Later in the point you again filled in the cell. Now how can one say that this row has been already copied or not. May be I am over thinking here
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Paul,

Although Rizvisa is trying to inquire more information, you are not really answering his questions.

Myself am confused about Master and Archive sheet and whether you want to copy or move.

I'm still asuming you want to copy newly added rows to the non-master and non-archive sheets and paste them to the Master sheet. My idea would be to do this when closing the file.

If this is OK then provide your workbook using a filesharing site like
www.speedyshare.com
so a more specific code can be written for you, since you said you don't have much experience with Excel.

Best regards,
Trowa