Referencing a password protected sheet while keeping the password secret

[Flash604]

I have a workbook that will sit in a folder that anyone in the company can access and which needs to be modified by supervisors, let's call it ProductionWB.

Management is able to pull a report from our timecard system that has everyone's raw data in it, let's call it TimecardWB.

I need to have ProductionWB pull some of the data from TimecardWB, which I have working just fine using SumProduct calls. Some of the other data in TimecardWB would be considered private and thus TimecardWB need to be password protected to prevent it being opened by anyone but management. This, of course, means that Production WB asks the use for the password to TimecardWB, but the users need to be prevented from ever knowing that password.

I've tried various VBA solutions I found in this forum to try and have it silently update ProductionWB when it's opened. If I can get them working I'd then password protect the VBA code so the TimecardWB password cannot be viewed. None of the solutions I've found on this forum seem to work, perhaps they all predate Office 2016.

Can anyone suggest a solution that would allow the TimecardWB to be password protected?

 

[Kyle123]

You’d be better having the timeCard workbook export the sanitised data to a third workbook and the production wb look at thaf

 

[Flash604]

I had thought of that, but in the end didn't see how it would prevent someone from accessing the original file; unless you're suggesting that the copy and paste the values from the third workbook into the ProductionWB whenever a new TimecardWB is produced.