Print Excel to PDF loop

[dgrimm]

I am trying to print an excel sheet to PDF but I keep getting errors. I am getting an error on the "ActiveSheet" line at the end.
I have tried various forms of this but they are all giving me an error.

Run -time error 1004 - Document not saved. The document may be open, or an error may have been encountered when saving

Sub MakeReportsPDF()

Dim DEPT As String
Dim EENAME As String
Dim EMPLID As Variant
Dim FINAL As String
Dim GENERAL As String
Dim INITIAL As String
Dim JOB As String
Dim NEXTSTEP As String
Dim POINTS As Variant
Dim WRITTEN As String
Dim DOH As Date
Dim SaveName As String
Dim ADate As String
Dim ARDATE As String
Dim Title As String
Dim User As String

User = Environ$("UserName")

Sheets("POINT LIST").Select
Range("A1").Select
lrow = Cells(Rows.Count, 2).End(xlUp).Row

For x = 2 To 10
    Sheets("POINT LIST").Select
    DEPT = Range("F" & x) & "-" & Range("G" & x)
    EENAME = Range("B" & x)
    EMPLID = Range("A" & x)
    FINAL = Range("N" & x)
    GENERAL = Range("P" & x)
    INITIAL = Range("L" & x)
    JOB = Range("H" & x) & "-" & Range("I" & x)
    NEXTSTEP = Range("O" & x)
    POINTS = Range("K" & x)
    WRITTEN = Range("M" & x)
    DOH = Range("D" & x)
    
    Sheets("Corrective Report").Activate
    Range("EENAME") = EENAME
    Range("EMPLID") = EMPLID
    Range("JOB") = JOB
    Range("HIRE") = DOH
    Range("DEPT") = DEPT
    Range("General") = GENERAL
    Range("PTS") = POINTS
    Range("INITIAL") = INITIAL
    Range("WRITTEN") = WRITTEN
    Range("FINAL") = FINAL
    Range("NEXTSTEP") = NEXTSTEP
    
    ARDATE = Range("ARDATE")
    
    SaveName = EENAME & "_Corrective Action Report_" & ARDATE
    
    Path = "C:\Users\" & User & "\CAReports\" & SaveName
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & ".pdf"

    
Next x
End Sub

 

[DanteAmor]

Hi @dgrimm, Thanks for posting on the forum.

ARDATE = Range("ARDATE")

Probably in the date you have the slash "/" and that is why it does not allow you to save the file, since the "/" character is not allowed in file names.
It is solved by replacing the "/" with a hyphen "-".

I allowed myself to review all your code and apply some improvements, check it out:

Sub MakeReportsPDF()
  Dim SaveName As String, sPath As String, EENAME As String, ARDATE As String
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim i As Long
  
  Set sh1 = Sheets("POINT LIST")
  Set sh2 = Sheets("Corrective Report")
  sPath = "C:\Users\" & Environ$("UserName") & "\CAReports\"
  
  For i = 2 To sh1.Cells(Rows.Count, 2).End(xlUp).Row
    EENAME = sh1.Range("B" & i).Value
    sh2.Range("EENAME") = EENAME
    sh2.Range("EMPLID") = sh1.Range("A" & i).Value
    sh2.Range("JOB") = sh1.Range("H" & i).Value & "-" & sh1.Range("I" & i).Value
    sh2.Range("HIRE") = sh1.Range("D" & i).Value
    sh2.Range("DEPT") = sh1.Range("F" & i).Value & "-" & sh1.Range("G" & i).Value
    sh2.Range("General") = sh1.Range("P" & i).Value
    sh2.Range("PTS") = sh1.Range("K" & i).Value
    sh2.Range("INITIAL") = sh1.Range("L" & i).Value
    sh2.Range("WRITTEN") = sh1.Range("M" & i).Value
    sh2.Range("FINAL") = sh1.Range("N" & i).Value
    sh2.Range("NEXTSTEP") = sh1.Range("O" & i).Value
    ARDATE = Format(sh2.Range("ARDATE"), "dd-mm-yyyy")
    SaveName = sPath & EENAME & "_Corrective Action Report_" & ARDATE
    sh2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SaveName & ".pdf"
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[dgrimm]

I am still receiving the same error on the export line. So I decided to go a different route and do a "Save As" as opposed to the "Print As" method and it worked.
I changed the last line to the recorded macro and changed the needed text so it looks like this now.

 ChDir "C:\Users\" & Environ$("UserName") & "\Desktop\CAReports"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\" & Environ$("UserName") & "\Desktop\CAReports" & SaveName, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False