Macro stopped working - error code

Question

This macro has worked for years but today it all of a sudden threw up the following error.

I am not very clever with VBA code but reading some of the websites it talks about declaring the sheet separately!! Can anyone help me fix the code below so that I can get it to work please.

Sub SavePDFRemittance()

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

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

pdfName = ActiveSheet.Range("G3") 

ChDir "F:\Confidential\0.  Cash\Remittances\" 'This is where you set a default file path. 

fileSaveName = Application.GetSaveAsFilename(pdfName, \_ 

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

If fileSaveName <> 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

I use this macro in many many spreadsheets so I wouldn't know what to do if I can't get it to work!!!!

Thanks in advance

Michelle

Answer 1

Hi,

There is specific forum support for VBA issues, you can post it in Stack Overflow. Reference: Office VBA support and feedback | Microsoft Docs

Answer 2

There are many VBA experts here, "go and ask somewhere else" is not an appropriate answer in my opinion.

The CHDIR in the code is superfluous and I like to see the file to check if there are special conditions that could cause this issue. The code itself looks good.

Andreas.

Answer 3

Thank you Andreas, I was wondering if things had changed and that was why I was being advised to go elsewhere :o)

My file is confidential cash flow/payments in and out - what would you like to see so I can strip it out.

Thanks

Michelle

Answer 4

My file is confidential cash flow/payments in and out - what would you like to see so I can strip it out.

Hi Michelle,

Make a copy of your file

Go into each sheet, select all cells and press DEL (the data is not important at this step)

In the sheet that is converted into a PDF copy G3 from the original file, so you have the same path in this file

Write some text into the print area like "qweqwe"
Run the code and see what happens

If the issue persits:

Zip your file

Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
Click Upload in the top and choose your file.
After uploading, right click the file and choose Share.
Click Copy Link in the lower left edge (no need to enter an email).
Copy the link and paste it here.

Andreas.

Answer 5

Hi Andreas

Apologies for the delay in replying, I've been on holiday for a few days.

I am now back in the office and have noticed that all my files that use the code above all have an error so I don't think it is file specific sadly.

Any ideas on a resolution at all?

Thanks

Michelle