Macro to copy worksheet to new book, then turn on protection

[Hydestone]

I have a spreadsheet that I want to send outside of my organization, so that people can fill it in them return the spreadsheet to me.
I'll set up protection to prevent unwanted modifications to the SS.

It would look something like this....

1. I edit a worksheet and get it ready to go out to people.
2. Then I would copy that spreadsheet into a new Excel file (there are other worksheets in the workbook that i can't share).
3. Then add protection to the worksheet.
4. Then email it to them.

Thoughts on how to write this macro? And how would it be activated? Could I put t button on the sheet to activate the macro...but the button would not copy over with the worksheet to the new workbook?

 

[Hydestone]

Just sent you the link! Thanks for taking a look.

 

[Hydestone]

I put this in the This Workbook module...is that the correct location? Not sure if that would have any impact on how the macro is working.

 

[mumps]

It should go in a standard module not in the module for ThisWorkbook. Remove it from it's current location and run it from a standard module.

 

[Hydestone]

Is a standard module one that is in VBAProject (FUNCRES.XLAM)?

 

[mumps]

Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

 

[Hydestone]

Great...works for me now. Thanks mumps!

 

[mumps]

My pleasure.