VBA code keeps debugging

Question

VBA code keeps debugging

Anonymous

Hi

Does anyone know why this code gives me the debug code please. It doesn't happen everytime I use it so its a sporadic issue. If I click on END and try again it works! Its the red writing below that is highlighted when it errors.

Sub SavePDF_PO()

'This macro opens the SaveAs option with the default file path "you have to set this file path below" coverts the whole sheet into .pdf file format

'And opens the .pdf to view <-- you can disable the view after covert option with lower code: OpenAfterPublish:=False

pdfName = ActiveSheet.Range("J4") 

ChDir "F:\Master\4  Supplier - Purchases\" 'This is where you set a default file path 

fileSaveName = Application.GetSaveAsFilename(pdfName, \_ 

fileFilter:="PDF Files (\*.pdf), \*.pdf") 

If fileSaveName &lt;&gt; False Then 

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= \_ 

    fileSaveName \_ 

    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas \_ 

    :=False, OpenAfterPublish:=False 

End If 

MsgBox "File Saved to" & " " & fileSaveName

End Sub

Thank you

Michelle

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Answer 1

Hi Michelle

If you have a defined file path and filename

I humbly suggest you not use/prompt the Save As dialog box, it is a waste of time.

You may try the following code

Sub SaveAsPDF2() 

''' This macro coverts the whole sheet into .pdf file format 

''' And will save it to a file path set by the User 

Dim pdfName As String, pdfPath As String 

pdfPath = "F:\Master\4  Supplier - Purchases\"      ''This is where you set a default file path 

With ActiveSheet 

        pdfName = .Range("J4").Value   ''' This variable stores the pdf filename 

        If pdfName <> "" Then 

             .ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath & pdffName & ".pdf", _ 

              Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False 

              MsgBox "File Saved to" & " " & pdfPath & pdffName 

        Else 

                MsgBox "File Name is Blank, Please check again" 

        End If 

End With 

End Sub

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Share via

VBA code keeps debugging

1 answer