Save as PDF VBA not working...

[Jav_uk8]

Hi All,

I am trying to create a macro to go through a list of dates (reports) and save them individually as PDFs in my area.

I get the following error:

Run-time error '1004':

Document not saved. the document may be open, or an error may have been encountered when saving.


Below is the macro used, I have used this macro before on a different report and it worked.


Sub Printing()

For i = 35 To 294
Sheets("Merthyr Printout").Select
Cells(81, 8) = Cells(i, 34)

Post_Script


Next i
End Sub


Sub Post_Script()


Sheets("Merthyr Printout").Select


crit = Cells(3, 34)
'Sheets("Merthyr Printout").Select


Dim temp As String
temp = "W:\People Report\Reports\Merthyr\Printout" & crit & ".pdf"


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



End Sub


Please help!!!

Cheers,

Jav

 

[Kenneth Hobson]

Welcome to the forum! When posting code, please do so between code tags. Click the # icon on toolbar to insert the tags.

Does temp contain a legal filename. When working with dates, "/" and "" mean folder delimiter. That is seldom what was intended.

e.g.

temp = "W:\People Report\Reports\Merthyr\Printout " & _
  Format(crit, "mm-dd-yyyy") & ".pdf"
Debug.Print temp

After you run code, Debug.Print shows results in the Immediate window. Select View to enable it if needed.

 

[Jav_uk8]

Hi Kenneth,

It works perfectly!

Thank you.

Jav

 

[Jav_uk8]

The code below is what I am using, the only problem now is every report run is saving as the same date (30/12/2016).

How do I continue the loop through the dates?

Sub Printing()                                              
       For i = 3 To 263
       Sheets("Merthyr Printout").Select
       Cells(81, 8) = Cells(i, 34)
       
 Post_Script


Next i
End Sub


Sub Post_Script()


crit = Cells(3, 34) 'For new dates, this will have to be amended.
Sheets("Merthyr Printout").Select




temp = "W:\People Report\Reports\Merthyr\Printout\ " & _
    Format(crit, "dd-mm-yyyy") & ".pdf"
    Debug.Print temp


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


path = "W:\People Report\Reports\Merthyr\Printout\" & crit & ".pdf"
            
End Sub

Cheers,

Jav