Error trying to run macro on protected sheet

[kyle4570]

I'm trying to run a macro that copies data from one workbook to several hundred others, and because the sheet in the several hundred that I want to copy to is protected, I of course get an error.

This sheet-level code isn't allowing me to circumvent the error. Where am I going wrong?

Please Login or Register  to view this content.

[Andy Pope]

That is a setting you would apply, each time a workbook is opened, to protect the workbook.
It is not a setting that will override any existing protection a user has in place.

For you code to work you will need to unprotect users workbooks.

[kyle4570]

OK. Got it. Once I use the macro to take hundreds of identical files and customize them with copied over data, how then can I get a macro into all of those customized files that is designed to protect a certain sheet automatically upon file save/close. Without opening them one at a time and creating the macro, that is. It's a chicken and egg thing.

[Andy Pope]

Confused. If you know the password to all these files why not simple use it to unprotect the worksheet, add your data and then protect the sheet again?

In order to get code into the workbooks you would need to write code using functions and methods from the vbe library.
http://www.cpearson.com/Excel/VBE.aspx

[kyle4570]

It's complicated, but the templates contain a macro that always keeps the sheet protected. I just need them unprotected as a one time thing so I can run the data copy macro. Then, whenever the file is opened again it becomes automatically protected again. I think I can do what I need if you could help me on some code that will unprotect just the "TargetWorksheet" sheet in all workbooks (stored in the same folder) that are named like this: FileCopy001.xlsm, FileCopy002.xlsm, etc.

[Andy Pope]

So do you know the password for the templates? If so you should be able to use Unprotect

[kyle4570]

Yep, I know the password. It's always the same. The issue is that there is no way around deploying the templates in anything but a protected state (well, the one sheet that gets protected, anyway), even if I wanted to because a macro inside forces protection on file close. And so I need to unprotect all as a one time thing before I apply all the copied data. Hundreds and hundreds of files. Can't open each one and manually unprotect. When the data copy macro finishes running, all files will save and close and be protected, which is just what is needed. There's a mix of users involved - some are authorized to tinker with the sheet and know the passwords, others not. I need to relieve the authorized tinkerers of having to remember to reprotect the sheets.

[Andy Pope]

You unprotect as part of the copy data macro.

psuedo code would be

for each template workbook
open workbook
unprotect sheet with known password
copy data
save and close workbook

[kyle4570]

Where would it get wedged in to this?

Please Login or Register  to view this content.

[kyle4570]

Figured it out. I was hoping I could accomplish it through the same macro that copies the data. And that's exactly what you helped me get to. Thank you.