Protecting Worksheets

[*Emma*]

Hi, Im new around these parts and can usually find my answer by just browsing through everyone elses questions but ive become a little stuck.

I'm doing estimates for the company i work for, when these are complete i forward them to the prospective customers, i have been doing this and hiding the cost information as we dont want any competitors getting wind of how we work out our prices.... so ive been protecting the worksheet, which we have all found works fine.

Until this morning where it has become apparent that if i copy the sheet and paste it into a new book you can unhide the columns without having to unprotect and enter a password...

is there a way i can get around this ?

I dont understand all this code stuff either so if thats what i need please explain where im to put it.

 

[GlennUK]

Copy values

Hi Emma,

once you've hidden the sensitive data, select the cells of the sheet, and then do Menu Command Edit/Goto/Special/Visible cells only, and then do Edit/Copy.

In a new workbook, do Edit/PasteSpecial/Values. You may also want to Edit/PasteSpecial/Formats too, to copy over any cell formatting.

This will remove the sensitive information.

 

[MichaelRo]

Using Code brings it's own problems - If Macro's are disabled the code becomes useless.

If you want to keep formula's, I would suggest rather than hiding columns within a sheet:

Link to a worksheet within the workbook.

You can then hide the worksheet and protect the workbook itself.

 

[*Emma*]

Hi, Thanks for your replies.

Ive created a new sheet with everything on it that i want the customer to see, ive then copied the values and formats over and all works fine, however im finding i have to copy the values and formats over every time i do a new quote for someone and also if i change something on that sheet the change doesnt automatically go over to the customers review page, is there anyway this can be done?

 

[GlennUK]

Do macro record of the process, and run that recorded macro when you want a new quote. If you need to tweak the code, then post another query on here.

 

[chef]

another suggestion....

create a sheet with all your calculations etc...eg called result

have another sheet called customer and use lookup to show the net prices etc without showing how you calculated them..

press F11 and click on very hidden with your worksheet called result and protect this..

that way the customer will only see their prices and cant unhide any sheet....you however can unhide the sheet and update as required

hope this make sense..
chef

 

[*Emma*]

Great, thanks for everyones help