Dear Excel experts,

I have designed a form to be filled out by various persons. At the end of the form, I have inserted a button that will:

1) Copy the Master sheet
2) Prompt the user to give a name to the new sheet, which will then create a copy of the entire Master sheet
3) Delete all the filled in fields on the Master sheet

So far, the code is the following:

Sub Button50_Click()

    ShName = Application.InputBox("Enter a new sheet name", "New Sheet", Type:=2)
    If ShName = "False" Then _
        If MsgBox("Sheet name not entered, abort?", _
            vbYesNo) = vbYes Then Exit Sub

    Sheets("Input").Copy After:=Sheets("Input")
    ActiveSheet.Name = ShName
    
    ActiveSheet.Unprotect Password:="OHG"
    ActiveSheet.Rows("60:60").Hidden = True
    ActiveSheet.Protect Password:="OHG", DrawingObjects:=True, Contents:=True, Scenarios:= _
    True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowDeletingRows:=True
    
    Dim Cell As Range
    Sheets("Input").Activate
    For Each Cell In Range("A1:J60")
    If Cell.Locked = False Then
    Cell.MergeArea.ClearContents
    End If
    Next
    
    Dim Shp As Shape
    Sheets("Input").Activate
    For Each Shp In ActiveSheet.Shapes
    If Shp.Name Like "Text Box*" Then
    ActiveSheet.Shapes(Shp.Name).TextFrame.Characters.Delete
    End If
    Next
    
    ActiveSheet.OptionButtons("Option Button 86").Value = 0
    ActiveSheet.OptionButtons("Option Button 87").Value = 0
    ActiveSheet.OptionButtons("Option Button 88").Value = 0
    ActiveSheet.OptionButtons("Option Button 89").Value = 0
    ActiveSheet.OptionButtons("Option Button 90").Value = 0
    ActiveSheet.OptionButtons("Option Button 92").Value = 0
    ActiveSheet.OptionButtons("Option Button 94").Value = 0
    ActiveSheet.OptionButtons("Option Button 95").Value = 0
    ActiveSheet.OptionButtons("Option Button 97").Value = 0
    ActiveSheet.OptionButtons("Option Button 100").Value = 0
    ActiveSheet.OptionButtons("Option Button 103").Value = 0
    ActiveSheet.OptionButtons("Option Button 105").Value = 0
    ActiveSheet.OptionButtons("Option Button 106").Value = 0
    ActiveSheet.OptionButtons("Option Button 107").Value = 0
    ActiveSheet.OptionButtons("Option Button 109").Value = 0
    ActiveSheet.OptionButtons("Option Button 110").Value = 0
    
End Sub
In order to diminish mistakes from users, I would like to have the new sheet's name defined automatically, based on 3 cell values in the Master sheet. These cells would be C16, C14 and C23. Ideally, each value would be separated by - (e.g. "Corporate - 36 - BMW").

My second issue would then be to sort all sheets alphabetically, keeping in mind that I do not know in advance how many sheets will be generated.

Finally, I would like to group the sheets according to part of their name (the part derived from C16) and then save them as a new excel document on the Desktop, with a predefined name. Again, I do not know in advance how many sheets will be generated. I have a partial code, which is the following:

Dim strPATH As String
    strPATH = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    Sheets(Array("Corporate - ? - ?")).Copy
    ActiveWorkbook.SaveAs strPATH & "/Sales Report ARB - Corporate Segment.xls"
Thank you so much for your help!

Best regards,

Luccin