Duplicate data from on tab to a master tab

Closed
pauljohn75 - Feb 8, 2013 at 07:20 PM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Feb 19, 2013 at 10:40 AM
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

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Feb 11, 2013 at 12:06 PM
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
0
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Feb 13, 2013 at 07:28 AM
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
0
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Feb 13, 2013 at 07:14 PM
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
0
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Feb 18, 2013 at 10:21 PM
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
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Feb 19, 2013 at 10:40 AM
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
0