Run Time Error 5 - In converting PDF sheets in a workbook into individual PDFs

[Sheila003]

Hello,

Please could I have some assistance with this macro.

I need to convert sheets into individual pdfs. The macro works to a point and then just stop converting the sheets and states Run time Error '5': Invalid procedure call or argument . Below is the code.

Sub SaveAsPDFOptions()

Dim Folder_Path As String

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select the Folder Path"

If .Show = -1 Then Folder_Path = .SelectedItems(1)

End With

Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets

sh.ExportAsFixedFormat xlTypePDF, Folder_Path & Application.PathSeparator & sh.Name & " Nov 24.pdf"

Next

Application.ScreenUpdating = True

End Sub

Much appreciated,

Kind regards,
Sheila

 

[Micron]

If you don't already know, learn how to step through your code (F8) to watch the flow and check the values of your variables. You should report which line causes the error. If you didn't because you're not aware, then stepping though should highlight the offending line. Then you can post back and id the problem line. I'm not seeing any obvious reason. Also, please post code within code tags to maintain indentation and readability (use vba button on posting toolbar).

 

[Micron]

Just a guess, but I suspect it is because you have one or more hidden sheets that are involved in the loop. If so, then you'll need to avoid them by name or by .Visible property.