macro to save to pdf with file path autofill from cell reference

Welcome to the Board.

Can you do it manually?

Thanks you for the welcome!

By this I assume you mean recording a macro?

I'm limited here because the file path information in the cells are an equation and I wouldn't know how to copy and paste their outcome into the Save As file line.

No, I mean can you save as pdf manually, with any valid name? If so what is the file name in your macro?

I am having the same problem! Some one please help if you know a solution. Here's my code for the Save as PDF part:

Sub Save_As_PDF()
'
' Save_As_PDF Macro
' Saves the report as a PDF
'

Dim SaveLocation As String
Dim Filename As String

SaveLocation = Sheets("Run %Favs & Save All Reports").Range("F1").Value
Filename = Sheets("Run %Favs & Save All Reports").Range("F3").Value

Sheets(Array("Summary Sheet", "Manager Items")).Select
Sheets("Summary Sheet").Activate
ChDir (SaveLocation)
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Filename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False

Sheets("Generate Manager Report").Select


I have a workbook set up that runs dynamic reports based on the names I put in. I have 1,000+ names that the macro needs to run through and generate reports for. After one report is generated, the macro uses the above code to save that report as a pdf to a folder on my desktop.

The macro works perfectly fine most of the time. It'll run through several names (200 - 300 names), generate the reports and save to a folder on my desktop without a problem and then it'll randomly throw the 1004 runtime error "Document not saved."

When I hit "Debug" it highlights this portion of the code:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Filename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False

But when I hit play again (to continue running the macro) it continues on without a problem for another 200 - 300 names before it throws the error again.

How can I keep it from throwing this error? I don't want to have to babysit the macro as it goes through 1,000+ names to make sure it hasn't thrown an error.

Thanks in advance!

You could try putting DoEvents at an appropriate place in your code.

I'm not familiar with the DoEvents function. I just did a search for it and based on the explanations I've found, I'm not sure how the DoEvents function would help.

It seems that the DoEvents function stops the macro for a period of time? I don't want the macro to stop. Or does the DoEvents allow the macro to keep running even if it encounters the runtime error 1004?

DoEvents yields execution so that the operating system can process other events. You won't notice the pause, but it may be sufficient fix your problem.

I had the same problem. The problem for me turned out to be a permissions problem.

I was trying to save the PDF to C:\ (temporarily) but manually trying to save it to PDF there reminded me that Excel doesn't like C:\ .

I pointed it to a different folder and it worked fine.