Refresh locked Pivot Table from external data and lock again

[Opust]

Hello All

I am new to macros, but I have the feeling I will have to learn a little bit if I want to complete my goal.

I am trying to create 6 workbooks where 5 of the workbooks would be locked, but needed to be updating from the sixth workbook which would act as a master (with 5 sheets on it). To complicate everything 1 column from the locked workbooks need to be editable without a password and feed back to the respected master sheet. I would like to achieve that the 5 locked worksheets update when they are opened and they get locked again. The master sheet will not be locked.

I think the task can be completed with excel's query feature. This would create a pivot table in each of the 5 workbooks. I can than specify to only lock the columns I want to be locked and leave the column that need editing open.

My problem is the locking, updating and re-locking of the sheets. Is there somebody who could help me with this?

I have been googling for a couple of weeks now and I am not one step closer to solve this issue. I have found some macros that should work, but unfortunately I could not get the locked sheets to update.

Thank you very much for reading this post.

[dflak]

I don't know if this is what you are looking for

Please Login or Register  to view this content.

With this code, you can lock the worksheets any way you want and the user has to live with it. However, VBA code will treat it as if the worksheet is unlocked.

[Opust]

Hello dflak

Thank you very much for your reply.

Unfortunately I am a total novice in code writing. I don't know how to implement the code you have kindly provided into a macro that would update the sheet.

Would you be so kind and help me with the rest of the code?

Thank you very much for your time.


Regards,
Opust

[dflak]

OK, but what I will need is a sample workbook. It will be easier to demonstrate with a concrete example. Show me what you want locked and show me what you need opened. I advise a full mock up with all the sheets you intend to use, but make sure you don't include any sensitive data. In fact, include just enough data to "prove" the concept - maybe a couple dozen records.

I will comment the code and point out where it might have to be customized but I'll make it as flexible as possible.

Here are instructions on how to attach a file.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Opust]

Thank you very much fro the quick response.

I have attached the files. I have only attached two files, as I hope I can replicate the code once we are done with it. It would be a good opportunity to learn.

I have locked the STA file to easily show which are the columns that I would like to lock and left column E unlocked as this has to stay open for the users.

This is also part of the problem. The information in column E in the STA file has to feed trough to the master file.

Again, thank you very much for trying to help. I am really grateful.

[dflak]

I'm going to regurgitate what you told me to make sure I understand it.

When I fill out something on the STA sheet in the master workbook, it will find the STA workbook and update columns A to E. When someone fills out column E, it will update the same row in column E on the STA sheet in the master file.

One question, can I assume that the 5 locked files are in the same directory with the master file? Or will they be somewhere else.

[Opust]

Hello dflak

Your understanding is 100% correct.

I am afraid all the files would be distributed to separate directories on the server.

The administrator editing the master file has access to all the other 5 directories. The users who are editing the E column have access only to their own assigned directories. I actually never thought about what kind of complications this could bring.

Thank you for pointing it out.

[dflak]

OK, I'll give you a "Control Panel" sheet so you can tell the program where to find the files.

If you want to have the files update the master, then they should have access to the directory in which the master file resides - if they "push" the data to the master. If the master file "pulls" the data, then this is not an issue.

I will assume the later unless you tell me otherwise I will assume the latter. You will have buttons to "push" the data to the files and to "pull" information from them.

[Opust]

This sounds amazing. Thank you very much for all the time you are putting into this.

[dflak]

My apologies for being a bit distracted. When you change the Master, do you want a complete rewrite on the subordinate files or append new data or only change what has been changed?

Also confirm the headers: some sheets have MENU others have Person, etc. This won't stop me from starting.

[dflak]

One more question. What makes a record unique? Is it the combination of Person and Date?

[Opust]

Hello dflak

I am very sorry for the late reply. I did not want to be impolite. I have been away from home and have not been able to get to my emails.

Updates may occur to data that already have been recorded on a previous version. I think it would be safer if the whole of the data would update.

The headers you see are not final, I would have to edit those once I am implementing the procedure. I will have a meeting on Monday with the managers responsible for the area this spreadsheet would support and I will try to confirm the headers.

I think the best would be the date and the supplier to identify each transaction. I will have this confirmed Monday as well. There is a spreadsheet that the managers use now and I am sure it is sorted in per reporting date.

I just would like to say that I am really grateful for all the help you are giving me. Thank you very much.


Regards,
Opust

[dflak]

There is a reason for asking the question if the titles are fixed, then I can use Excel Tables, otherwise I'll treat them as regular ranges. The latter will work in any case, just marginally harder to code. I've been chasing my tail recently. But I should get it "delivered" by Monday.

[Opust]

Hello dflak

Sorry, I have not come back sooner. Hope you had some time to catch up. I had some issues with the managers, on what they would like to see in the headings. As always, coming to an agreement with 5 people can be quite difficult. I have attached the headings that they would like to see to this reply. Unfortunately the size of the table had doubled, to the one I have been told originally.

They have also confirmed that the "Date Reported" and "Main Contractor" coulombs are the ones that would describe the best a record.

I have been asked to change the "Oxendon House" coulomb to "Maintenance Issue".

Is there anything else you would like to know?


Regards,
Lajos

[dflak]

I will make it a point to get to it today. It's odd, since getting laid off, my days are busier then when I was working! I actually have to schedule things or they don't get done.

[Opust]

Hello dflak

Sorry to hear about your dismissal. I am sure a person with your skillset will not find difficult to quickly find a new position. I wish you good luck with it.

Please do not feel pressured by me. I am already in your debt for spending so much time on "my" project.

[dflak]

I think I have it.

In the master workbook, on the control panel sheet, there is a configuration table. This is where you tell the program where the source files are located. In my example, I have them all in the same directory.

I have included the source files. The source files must me named to match the sheet names. Each source file has a table that reflects the sheet name. The program depends on the sheets and files and the table names matching up.

In both the source and Master workbook, the last column is Composite. This is the way the program can tell what records exist and find them. You can hide this column if you don't want to see it.

I have a recommendation for each of the source workbooks to add data validation I suggest a date validation less than or equal to today for the Date Reported. A date greater than or equal to today for Date Contractor will visit ... and another date validation on Date Confirmation ... Finally Home advised Y/N.

When you click on the button on the Control Panel sheet, the program will go to each of the source files and look at the composites and compare them to the composites on the sheet. If it finds the composite in the Master sheet, it updates it. If not, it adds it to the end.

Give this a try and we can move on from there.

[Opust]

Woo. That was really quick.

It looks really great and it is very clever.


I have a few questions. Coulomb C on every sheet should say "Maintenance Issue". Can I just rename it?

The process we would like to implement would be as below.
1. The administrator fills in the relevant Master sheet from coulomb A to I.
2. The software transfers the data to the relevant source.
3. Manager fills in the completion date on the source sheet. Coulomb J.
4. The master file pulls data from coulomb J to the master sheet.

I have tried these steps on the sheets you have sent and step 3 and 4 was working perfectly.

Part of the source documents (Coulomb A to I) would be permanently locked to prevent alteration by the managers. Only the coulomb J would be editable on the source sheet.

Please do not think I am complaining, this is a very clever setup. However, I have been tasked to find a way to get the above process working. Do you think that it would be possible?

[dflak]

Yes, you can simply rename it.

I will have to do more work. I got the "pull" working from the source files, I did not get the "push" to them. I'll work on that.

[dflak]

OK. I think I got it right this time.

When you click the send data button on the master sheet, each of the subordinate sheet tables are cleared completely and the new data is copied in. The sheets are protected and only column J is available to the manager.

When you click the read data button on the master sheet, the dates are read from the subordinate sheets and copied in. It is assumed that no new projects were added in between send and read. Dates are copied in into the same rows as when they were sent. If this is not true, there's a bit more coding I have to do.

There is test data in all the sheets now, but the first time you send real data, the system will be in order.

One more thing: the Password is "Password" This is set in this line in the code

Please Login or Register  to view this content.

If you change it, you might have to change it in the individual books.