Macro to re-protect sheet w/ original password after pasting valuesin new book

[PatBurke81]

Hello,

I have built a large file with many hidden and password protected
sheets and formulas. The end user of the file manipulates several
cells on two visible sheets and then sends the entire workbook back to
me. I only need a hardcoded record of the two sheets they manipulate.

To save bandwidth, I am trying to build a button that will copy the
two sheets to a new workbook and paste the values on those new
sheets. This will allow the user to send me a much smaller file in
return. I am getting stuck with the password protection, as I want
both workbooks to remain protected upon completion of the macro. I
have built the command button and simply can't figure out how to
unprotect and then re-protect (with the original password after
pasting the values) the two tabs in the new workbook. Any help is
appreciated.

Thanks.

 

[Per Jessen]

Hi

I think it's something like this you need:

Sub test()
Dim New_Wb As Workbook
MyPass = "InitPass"
Set New_Wb = Workbooks.Add
'Copy data
New_Wb.Protect Password:=MyPass

New_Wb.Close
ThisWorkbook.Protect Password:=MyPass

End Sub

Regards,
Per