EXCEL :: Exporter un onglet/plage de cellules dans un fichier PDF

Option Explicit
Sub MAIN(Optional sDumy As String)
    Dim vAppel As Variant
    Dim sType As String
   
    On Error GoTo HANDLE_ERROR

    sType = TypeName(Application.Caller)
    Select Case sType
        Case "Range"
            vAppel = Application.Caller.Address
        Case "String"
            vAppel = Application.Caller
        Case "Error"
            vAppel = sDumy
        Case Else
            vAppel = sDumy
    End Select

    Select Case vAppel
        Case "SH_SAVE_PDF"
            Call SAVE_TO_PDF
    End Select

FIN:
    MsgBox "Fiches imprimées"
    Exit Sub
   
HANDLE_ERROR:
    MsgBox "Erreur " & Err.Description
    Resume FIN
End Sub

Sub SAVE_TO_PDF()

    Const WK_FICHE = "Fiche de stock"
    Const FOLDER = "D:\DATA\10__DEVELOPPEMENTS__EXCEL__LABS\§__LAB04_FICHE_STOCK_PDF"
    Const LIST_FRUITS = "LIST_FRUITS"
    Const LIST_FRUIT_UNIQUE = "LIST_FRUIT_UNIQUE"
   
    Dim wks As Worksheet
    Dim rFruit As Range
    Dim rList As Range
    Dim lngRowsCount As Long
    Dim iFruit As Long
    Dim sFilePathPDF As String
    Dim sTempFile As String

    Application.ScreenUpdating = False
    Set wks = ActiveWorkbook.Worksheets(WK_FICHE)
    Set rFruit = wks.Range(LIST_FRUITS)
    Set rList = Range(LIST_FRUIT_UNIQUE)
    sFilePathPDF = FOLDER & "\" & "Fiche_De_Stock_[PARSE].pdf"
    lngRowsCount = rList.CurrentRegion.Rows.Count - 2
    For iFruit = 1 To lngRowsCount
        rFruit.Value = rList.Offset(iFruit - 1, 0).Value
        sTempFile = Replace(sFilePathPDF, "[PARSE]", rFruit.Value)
        wks.ExportAsFixedFormat _
             Type:=xlTypePDF, _
             Filename:=sTempFile, _
             Quality:=xlQualityStandard, _
             IncludeDocProperties:=True, _
             IgnorePrintAreas:=False, _
             OpenAfterPublish:=False
    Next iFruit
    Application.ScreenUpdating = True
End Sub