Exporting multiple excel sheets into seperate PDFs spits out Runtime Error 5

Question

I need to creat a macro, which exports each worksheet into a seperate PDF with the name of the worksheet as the new filename of the PDF. When I run the macro it spits out Runtime Error 5: Invalid Procedure call or argument

Her's my code:

Sub Makro1()

Dim Folder_Path As String
With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Ordner zum Speichern der PDFs auswählen"
    
    If .Show = -1 Then Folder_Path = .SelectedItems(1)
    
End With

Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets

    sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Folder_Path & Application.PathSeparator & sh.Name & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next

MsgBox "Fertig!"

End Sub

It seems to run into the Error on the ExportAsFixedFormat action, but I don't see why.

Answer 1

Please, try the next adapted code. It previously check the sheet visibility and according to its status, from this point of view, unhide, export and hide it as it initially was:

Sub Makro1()
 Dim Folder_Path As String

 With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Ordner zum Speichern der PDFs auswählen"
    
    If .Show = -1 Then Folder_Path = .SelectedItems(1)
 End With

 Dim sh As Worksheet, shVis As XlSheetVisibility, boolVis As Boolean

 For Each sh In ActiveWorkbook.Worksheets
    With sh
        If .Visible <> xlSheetVisible Then   'if it is hidden
            shVis = .Visible                            'memorise its visibility
            sh.Visible = xlSheetVisible: boolVis = True 'make it visible and boolVis True
        End If
        'export the (visible) sheet:
        .ExportAsFixedFormat Type:=xlTypePDF, fileName:=Folder_Path & _
                Application.PathSeparator & sh.name & ".pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
                
        If boolVis Then .Visible = shVis: boolVis = False 'change back the initial visibility and reinitialize boolVis as False
    End With
 Next

MsgBox "Fertig!"

End Sub

Not tested, but it should solve the hidden sheet issue.

If still a problem appear, please identify it doing the same examination, moving the cursor over the respective sheet name and see what is it about. If a protected sheet, for instance, the code can be adapted to also overpass such a situation...