Rename worksheet name "Sheet1" in VBA

here are four codes you can use

Sub Macro2()
'
ActiveSheet.Name = "File1"
End Sub
Sub Macro3()
'
ActiveSheet.Name = Range("a1")
End Sub
Sub Macro6()
'
Sheets(1).Name = "File1"
End Sub
Sub Macro7()
'
Sheets(1).Name = Sheets(2).Range("A1")
End Sub

Hi

If you use Workbooks.Add 1 then you will create a new workbook with just 1 sheet, which you can then delete at the end.
Maybe something like

Private Sub CommandButton1_Click()
    Dim wbNew As Workbook
    Dim src As Workbook
    Dim sh As Worksheet
    Dim myarray As Variant
    Application.DisplayAlerts = False
    Workbooks.Add 1
    Set wbNew = ActiveWorkbook
    Set src = ThisWorkbook
    For Each sh In src.Worksheets(Array("dataExtract", "pt"))
        sh.Activate
        myarray = sh.UsedRange.Value
        wbNew.Sheets.Add.Name = sh.Name
        wbNew.Sheets(sh.Name).Range("A1").Resize(UBound(myarray), UBound(myarray, 2)) = myarray
    Next sh
    wbNew.Sheets(1).Delete

    wbNew.SaveAs Filename:="something.xls"
    wbNew.Close
    Application.DisplayAlerts = True
End Sub