Howw to Copy master sheet as values and automatically set new name

How to copy master sheet as values and automatically set new name

Requirement:

The user has looked for VBA on the Internet that copies the data from the master sheet to a new sheet of the same workbook but does not fit my needs.

The user is looking for someone to edit this VBA code below and customize the conditions required.

The user needs Master sheet template copy to new sheet as values without formulas, color cells, comments and automatically set new name from cell.
Some cells are merged, also contain comments and formulas.

Conditions:

1. Copy the template Master sheet immediately after the sheet Master in same workbook
2. Read data from cell Z5 and set as the new Sheet name (format dd.mm.yy)
3. Paste multiple print area (do not paste other data from Sheet)
4. Paste data as values/text without formulas, comments and cell color (format cells need be copied)
5. If there is already a sheet with the name, display warning and cancel copy
6. If the user cancels the copy, exit from VBA and do not copy nothing

The user is using this VBA code:

Code:

Sub CopyMasterAsValuesAndSetNewName()
    Dim sName As String
    Dim wks As Worksheet
    
    Worksheets("Master").Copy after:=Sheets(1)
    Set wks = ActiveSheet
        Do While sName <> wks.Name
            sName = Application.InputBox _
              (Prompt:="Write new sheet name")
            On Error Resume Next
            wks.Name = sName
            On Error GoTo 0
        Loop
    Set wks = Nothing
End Sub

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148293-copy-master-sheet-as-values-and-automatically-set-new-name

Solution:

Code:

Option Explicit

Sub CopyMasterAsValuesAndSetNewName()
     
    Application.ScreenUpdating = 0
    Sheets("Master").Copy , Sheets("Master")
    With ActiveSheet
        On Error GoTo GetOut
        .Name = Format([z5], "dd.mm.yy")
        On Error GoTo -1: On Error GoTo 0
        .Columns(34).Resize(, 100).Delete
        With .UsedRange
            .Interior.Color = xlNone
            .ClearComments
            .Validation.Delete
            .Value = .Value
        End With
        Application.Goto .[a1]
        ActiveWindow.DisplayGridlines = 0
    End With
    Exit Sub
    
GetOut:
    MsgBox "A sheet named """ & Format([z5], "dd.mm.yy") & """ already exists.", , "Sheet Exists"
    Application.DisplayAlerts = 0
    Sheets(2).Delete
    Application.DisplayAlerts = 1
    Application.Goto Sheets("Master").[a1]

End Sub

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.