VBA When attempting to export an excel sheet with ActiveSheet.ExportAsFixedFormat I get RunTime Erro-CodePudding

I simply want to unhide a sheet that contains a report then export that sheet as a pdf then hide it once again and go back to another sheet, however when running this code I receive a "RunTime Error 1004 Application-defined or object-defined error"

Sub Print_To_PDF()


Sheets("Report").Visible = True
Sheets("Report").Select

Dim pdfname As String

pdfname = Range("A1").Value


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

Sheets("Report").Visible = False
Sheets("Summary").Select

End Sub

It works sometimes and even on other computers not sure why I am getting this error. Can someone please help.

CodePudding user response：

This works fine for me:

Sub Print_To_PDF()
    
    Dim pdfname As String

    pdfname = Range("A1").Value 'ideally specify a worksheet here!
    
    With ThisWorkbook.Worksheets("Report")
        .Visible = True
        .ExportAsFixedFormat Type:=xlTypePDF, _
           Filename:=pdfname, Quality:=xlQualityStandard, _
           IncludeDocProperties:=False, IgnorePrintAreas:=False, _
           OpenAfterPublish:=True
        .Visible = False
    End With

End Sub

CodePudding user response：

As best practice in the Excel object library (VBA or any COM interfacing code), always qualify objects such as Workbooks, Worksheets, Ranges, QueryTables, PivotTables, and other objects to the parent context. See this canonical post, How to avoid using Select in Excel VBA for readability, maintainability, and reliability.

Therefore, consider explicitly qualifying each object or use a With...End With block. Additionally, consider proper error handling which can provide further information for your debugging needs.

Sub Print_To_PDF()
On Error Goto ErrHandle:
    Dim pdfname As String

    With ThisWorkbook.Sheets("Report")
        .Visible = True
        pdfname = .Range("A1").Value

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

        .Visible = False
    End With

    ThisWorkbook.Sheets("Summary").Select

ExitHandle:
    Exit Sub

ErrHandle:
    Msgbox Err.Number & ": " & Err.Description, vbCritical
    Resume ExitHandle
End Sub