Editar:

Question

Agregué un botón en la hoja de Excel y ahora, cuando hago clic en ese botón, guarda la hoja en una ruta específica con un nombre específico pero en formato pdf.

Quiero guardar esta hoja simplemente en formato Excel (.xlsx). entonces

Sub PDFActiveSheet2()

Dim ws As Worksheet
Dim strFile As String

On Error GoTo errHandler

strFile = "m:\formats\" & Range("H8")
Set ws = ActiveSheet

ws.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

MsgBox "file has been created."

exitHandler:
        Exit Sub
errHandler:
        MsgBox "Could not create the file"
        Resume exitHandler

End Sub

¿Qué necesito cambiar?

Answer 1

Cambie su siguiente código de

ws.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

Al siguiente código

ActiveWorkbook.SaveAs Filename:="C:\Users\46506090\Desktop\Book1.xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Cambiar la ruta (C:\Usuarios\46506090\Escritorio\Book1.xlsm) a lo que le gustaría y ver si funciona.

CreateBackup:=False  

esto es opcional

Editar:

El código completo

Option Explicit

Sub Button1_Click()
'Sub PDFActiveSheet2()

Dim ws As Worksheet
Dim strFile As String

On Error GoTo errHandler

strFile = "m:\formats\" & Range("H8")
Set ws = ActiveSheet

ActiveWorkbook.SaveAs Filename:="C:\Users\46506090\Desktop\Book1.xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'Comment: Replace "C:\Users\46506090\Desktop\Book1.xlsm" to your desired filename
MsgBox "file has been created."

exitHandler:
        Exit Sub
errHandler:
        MsgBox "Could not create the file"
        Resume exitHandler

End Sub

Answer 2

Corrección a otra respuesta, solo debería haber una ubicación para guardar

Option Explicit

Sub Button1_Click()
'Sub PDFActiveSheet2()

Dim ws As Worksheet
Dim strFile As String

On Error GoTo errHandler

strFile = "C:\Users\yourName\Desktop\Book1.xlsm"
'Comment: Replace "C:\Users\yourName\Desktop\Book1.xlsm" to your desired filename

Set ws = ActiveSheet

ActiveWorkbook.SaveAs Filename:=strFile, _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

MsgBox "file has been created."

exitHandler:
        Exit Sub
errHandler:
        MsgBox "Could not create the file"
        Resume exitHandler

End Sub