VBA Code assigned to CommandButton

[mio306]

In three sheets I have a button CommandButton_Click(). I wrote, in all three sheets, this code and I assigned at all the three buttons. Pressed the button that copies the active sheet a new wrokbook.

Private Sub CommandButtonSave_Click () 
... 
... 
ActiveSheet.Copy 
With ActiveSheet.UsedRange 
. Copy 
. PasteSpecial xlValues 
. PasteSpecial xlFormats 
End With 
ActiveSheet.Shapes (CommandButton1). Delete 
Application.DisplayAlerts = False 
ActiveWorkbook.SaveAs FileName: = NewName, FileFormat: = xlWorkbookNormal 
Application.DisplayAlerts = True 
ActiveWindow.Close 
..... 
..... 
End Sub

The solution I do not like it because:
1. Same code is replicated three times , written in the Sheet1, Sheet2 and Sheet3. I would write it in one place.
2. In the couple of the sheet, the macro deletes the CommandButton, but the code remains in the VBA project . I would like to have the new worbook without VBA code.
I tried to solve the problem by writing code in ThisWorbook, but I do not know how to assign it to any one of the buttons CommandButton_Click (), in three different sheet.
Someone could help to solve this problem? Thanks in advance.
Mio

 

[GlennUK]

Use Forms buttons instead. You can assign the same macro to each, and the code must be stored in a Module, and not in the sheet code area, and so will not be copied.

 

[mio306]

I solved this way. Thank you!

 

[GlennUK]

That's great!