Use VBA to write VBA in new worksheet

[BODYCOTE]

I currently have a master sheet that costs drinks, when it is ready I hit 'Save Duplicate' button and it copies the worksheet to the left of the current sheet and renames it based on cell 'B2'

Public Sub CopySheetAndRenameByCell()
        ActiveSheet.Select
        ActiveSheet.Unprotect Password:="lavish123"
Range("h17").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(B2,Table60,4,FALSE)*1000"
Range("D4:D14").Sort _
Key1:=Range("D4"), Header:=xNo
        ActiveSheet.Select
    ActiveSheet.Protect Password:="lavish123", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Dim wks As Worksheet
    Set wks = ActiveSheet
    ActiveSheet.Copy After:=Worksheets("****TAIL COSTER")
    If wks.Range("B2").Value <> "" Then
        On Error Resume Next
        ActiveSheet.Name = wks.Range("B2").Value
    End If
 
    wks.Activate
   
    Sheets("****TAIL COSTER").Select
    Range("B2:m2").Select
    ActiveCell.FormulaR1C1 = "*****TAIL NAME HERE*"
    Range("B4:d14").Select
    Selection.ClearContents
    Range("C19:H19").Select
    Selection.ClearContents
    Range("B22").Select
    Selection.ClearContents
    Range("B24").Select
    Selection.ClearContents
    Range("B26").Select
    Selection.ClearContents
    Range("f17").Select
    Selection.ClearContents
    Range("h17").Select
    Selection.ClearContents
    Range("d19:i19").Select
    Selection.ClearContents
    Range("h17").Select
    Selection.ClearContents

End Sub

I also want the 'save duplicate' vba to use the following in the sheet to allow 'B2' to control the value of the worksheet name:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("b2")) Is Nothing Then
        ActiveSheet.Name = ActiveSheet.Range("b2")
    End If
End Sub

is it possible for my button to edit vba sheets?

 

[Marc L]

​

If the source sheet contains the VBA event procedure so the new sheet has it also …​

 

[BODYCOTE]

​

If the source sheet contains the VBA event procedure so the new sheet has it also …​

I don't want the source sheet to contain the vba as the sheet name must always remain '****TAIL COSTER'