How to copy format and value only?

[London12F]

The VBA below can form a new sheet and then copy used range of existing sheet to this new sheet.

Sub yMacro1()

    Dim wsNewSheet  As Worksheet
    Dim wsMySheet   As Worksheet
  
    Set wsNewSheet = Sheets.Add(Before:=Worksheets(1))
    
    For Each wsMySheet In ThisWorkbook.Sheets
        If wsMySheet.Name <> wsNewSheet.Name Then
                wsMySheet.UsedRange.Copy Destination:=wsNewSheet.Range("A1")
        End If
    Next wsMySheet
End Sub

How to revise the VBA so that it copies format and value only, (instead of copy all)?

 

[Kenneth Hobson]

Do a copy and a pastespecial xlpastevalues and another for copy and pastespecial xlpasteformats. e.g.

wsMySheet.UsedRange.Copy
wsNewSheet.Range("A1").PasteSpecial xlPasteValues
wsMySheet.UsedRange.Copy
wsNewSheet.Range("A1").PasteSpecial xlPasteFormats
Application.CutCopyMode = False

 

[London12F]

Thanks Kenneth Hobson for suggestion.