Hi All
I am hoping someone will be able to help.
I am currently working on a performance document. I am working on the principle of having a master template which people can then access, Save As, and use to monitor performance around KPI’s.
The issue I am having is around the name of the document changing when it is saved as, as all records have to retained. The macro I am struggling with is designed to unlock the workbook & worksheet, copy the worksheet specified into a new workbook and then return to the workbook the macro is held within and lock it back up. However, when the name changes it just locks the new workbook rather that the version I am asking it to.
ActiveWorkbook.Unprotect Password:="KPIreview"
ActiveSheet.Unprotect Password:="KPIreview"
Sheets("EID Graphs").Select
Sheets("EID Graphs").Copy
Windows( _
"Kent - Monthly Activity & Performance Review - Version 17 - Master.xlsm" _
).Activate
ActiveSheet.Protect Password:="KPIreview", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Password:="KPIreview", Structure:=True, Windows:=False
Is there anyway I can change the “Kent – Monthly Activity & Performance Review – Version 17 – Master.xlsm” statement within the macro to reflect the change in name of the document?
Thank you in advance for any help you can provide.
Bookmarks