Setting Up Passwords For Different Tabs--Each Person Can Only View One Tab

Hello,

I'm trying to find a way to perform a task much more efficiently through the use of macros. Here's the gist....

1) I have one tab in Excel with a bunch of accounting data--listed out by Cardholder names (about 400); the first step of the process is to separate out the data into tabs by Cardholder name--I've got a macro that will do this, so I think I'm set here.

2) Next, I need to send out a link to this workbook (shared) to these 400 people. What I would like is to have them enter a unique password that would only allow them to view the tab with their accounting information on it (and none of the others) when they click the link. They would enter some data, and then save.

3) I would then need to take all the data (from all 400 tabs) and consolidate them back into one tab.

Step #1 is fairly simple. Is Step #2 possible? I know it's possible to set up 400 different passwords for people; however, the idea is to not allow anyone to view a tab that doesn't contain their personal accounting data. Lastly, after this workbook has been used for the first month, is it possible to have Step #1 separate out the new monthly data into the same tab that was created the month before (just replacing the old data)--instead of creating all new tabs? I'm asking because it seems like you would have to set up new passwords in this case (I may be wrong though).

Anyway, sorry for the long story. If anyone has any suggestions, or can tell me that this isn't possible, either way it would be very helpful--as this process is extremely time-consuming using our current method.

Thanks in advance!

Kerry

Yes you can do what you suggest in step2 but it wouldn't be particularly secure. I would suggest if you are dealing with accounts then separating each sheet into a separate workbook with a password to open the workbook and then consolidating the workbooks back into one after sending them out would be a better way to go.

Note, you can do this with something like:

Please Login or Register  to view this content.

Although you would need to add some sort of look up tables for the passwords.