I have 5 people working on a shared excel workbook and each one updates
data (basically its a tracker) on his worksheet. I want that all their
data gets transferred to the master sheet every day before they break
off duty.(maybe with button for update data on their individual
What i am aiming for is that there is no scope for mischief for e.g.
one team member deletes data from another colleagues worksheet
(inadvertently). I want to password protect each sheet individually and
give each individual her/his password only and i have all the passwords
including for master sheet (which nobody else will have).
Before we start creating complex solutions using Excel VBA let's see
how we can solve Naveen's problem without VBA.
Microsoft Excel has an interesting feature called compare and merge
workbooks hidden in the options area.
Click on file
In the Excel Options window select Customize Ribbon
Under 'chose command from' option select 'all
commands' from the drop-down menu
Select Compare and Merge Workbooks
On the right hand side below the main tabs click on
New Group to create a New Group under the Home tab
Now click on 'Add>>' to bring the
option 'Compare and Merge Workbooks' under the new group in the Home tab
You will now see the Compare and Merge Workbooks
Option in your home tab under a New Group
Once the compare and merge workbooks option has been added we do the following:
Create a file and save it as a master file
convert it into a shared workbook by clicking on Review and under the
Changes Group we select 'share workbook' and then check 'Allow changes
by more than one user at the same time. This also allows workbook
In the 'advanced tab' in the Share Workbook window under 'Track change' we can change 'Keep change history for... days' option.
Now we make as many copies of the master file as required and save them with appropriate names.
Next the team members receive their copy where they can make their entries or changes.
The workbook of the team member is then closed.
We open our master workbook or file and click on 'comapre and merge workbooks,,,'
In the new window 'Select Files to Merge into Current workbook' we select the relevant file and click OK
The data from that file is merged into the master file
We do this with files from all the team members to get all the data into the master file