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.

 

[Sheila003]

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.

Thank you for your time in reply to my message. You are right there was hidden sheets.

It works perfectly now.

Kind regards,

Sheila

 

[Micron]

Glad I could help. If you have a solution it's nice to mark your thread as solved. At the very least, doing so takes it off the list of threads needing to be solved. It also helps others who might have the same issue.