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?

 

[mumps]

Insert a button (click Insert...Shapes in top menu). Place this macro in a standard module and assign it to the button. It will create a new workbook without the button that you can save and then email as a attachment.

Sub createNewWorkbook()
    ActiveSheet.Copy
    Dim shp As Shape
    With ActiveSheet
        For Each shp In .Shapes
           shp.Delete
        Next shp
        .Protect Password:="MyPassword"
        .EnableSelection = xlUnlockedCells
    End With
End Sub

 

[Hydestone]

Awesome, short and sweet! Thanks!

 

[mumps]

You are very welcome.

 

[Hydestone]

So I added this to the code and it worked well...except in reverse!

Is there a way for the original sheet to remain as is and the new copied worksheet to have the button removed and sheet protected?

 

[mumps]

That is exactly how the macro should be working as written. It should be creating a new workbook with the copied sheet. The button should be deleted and the sheet protected. If it isn't doing that, please explain what it is doing.

 

[Hydestone]

I was hoping to have the original worksheet remain as is after the button is clicked. So the button would remain and the sheet would remain unprotected.

The newly created worksheet would be in a new workbook, be protected, and not have a button.

 

[mumps]

As I mentioned before, the macro should be doing that as written. If it is not, then perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Hydestone]

OK, I'll see if I can upload it.

It copies the sheet to a new workbook. But the removal of shapes and locking of the spreadsheet is not happening on the newly created sheet. It is happening on the original source sheet.

It seems to be copying active sheet, then removing shapes and adding protection to the active sheet - which is not the newly created sheet.

 

[mumps]

When the new workbook is created, the sheet in that workbook becomes the active sheet so the button form that sheet should be deleted and that sheet protected. I don't know why it's not working that way for you. I'll wait for you to upload the file.