excel vba - creating password backup workbook in Save event

[terriertrip]

---
Below is code to save a backup copy to a folder. Every time I try and code in a password for a copy, either both the copy and the original workbook require the password to open, or I have the syntax incorrect. I don't want a password for the original wb, just the copy wb.

How can I rewrite this so I could keep this within the BeforeSave event?

Please Login or Register  to view this content.

[TMS]

SaveCopyAs doesn’t have the option to provide a password. You'd need to use SaveAs. If you want two copies, use Save and then SaveAs.

[terriertrip]

I've tried that but if I Save, then SaveAs, the SaveAs copy is the active workbook. How can I close the copy without having to close the workbook?

[TMS]

Ok, maybe SaveAs backup file, then SaveAs original file (with Application.DisplayAlerts = False)

[terriertrip]

Please Login or Register  to view this content.

Excel doesn't like that apparently. Crashes the program - I imagine it has to do with setting the first SaveAs with password.

However, what the routine produces requires a password for both backup and original - obviously because the first SaveAs tags the wb with a password, so the next SaveAs is saving the document that coded the password. I sure wish SaveAsCopy would have the capability to assign a password.