Macro: copy to new sheet that is locked or password protected

I am working on a macro to automate an equipment charge-out form. We are a small environmental firm who uses equipment in environmental clean-up, for which we charge our clients. This is meant to make an invoice for them out of our master equipment list, to prevent wasting paper and sharing rates unnecessarily. It should only copy the rows related to the equipment that project used.

So far:
There is a button to run the macro, which reads sheet1 (named 'DO NOT PRINT') and checks column E. If there is any value in column E, then it copies that entire row to sheet2 (named "PRINT FORM').

It copies values, formatting, and the password protection we have on all unshaded cells.

What we are looking for:
A change that would make sheet2 fully protected, so no cell can be edited after it is copied there, or a 'print only' version (for legal purposes in environmental clean-up jobs).

Is this possible? The current password in the workbook is INTEP

This is the great code submitted to us by AlphaFrog:

Please Login or Register  to view this content.

Hmm. Maybe I am not using it correctly.. I altered the overall code to say this, inserting the suggested code where I thought it might go:

Please Login or Register  to view this content.

When I run it, I can still edit cells in sheet2.
We are trying to make it work so that no cell in sheet2 can be edited once copied there.
Do you think I need to place this new piece differently? Or do I need something different?
Thanks for your help so far,

j

Thank you. Okay. I understand that there is a solution, but I am not sure how to apply it.

The way a person would use sheet1 would be one of our geologists or hydrologists would enter quantity and dates used for a certain piece of equipment. Sheet1 has is protected so that only shaded cells are able to be edited. I think I am misunderstanding some difference between locked and password protected. If I enter the password, I can edit unshaded cells, but other employees would not have that password.

What we are trying to do is have a workbook set up in which a person can enter things into sheet1, only the shaded cells.
When he or she clicks the macro button, the rows they actually used will copy to sheet2 but not be editable after that.

So, the part I don't quite follow, is how I can make sure all cells are "locked before protecting the sheet"?

Could the code sequence be something like this?
(Considering that the sheet is password protected when an employee logs in and can only edit shaded cells):
1. Employee enters quantitites and other needed information into shaded cells.
2. Employee clicks button

The macro then
1. makes sure all cells are locked, (is this the part I am missing?)
2. checks column E of sheet 1
3. any rows with values are copied to sheet2
Then the employee can print sheet2, but not edit any cells in sheet2?

Any clarification would be appreciated.

I finally followed everything in the correct sequence to get the results I was describing! Thank you so much. And especially thank you for explaining it more than one way, since I was not following.

I think the first time I tried it, I was confused because it copied values and not formatting, which looks a bit ugly. (it was a very busy time with other projects, now I have the time to focus on this again).

However, your suggestion to lock all cells in sheet 2 and then substitute this piece:

Please Login or Register  to view this content.

This did copy the indicated rows into a fully uneditable sheet2, which is the important part.

I do still need to copy values AND formatting. If I cannot figure that out soon, i will likely post it again and see what the forum thinks!

The updated sheet with the full macro is attached.