XL 2010 Enregistrer un fichier Excel complet en pdf.

Sub Tst()
Dim sNomFichierPDF As String
Dim i As Long, Cpt As Long
Dim Ar() As String, Wsh As Worksheet
Dim Plage(4) As String

    sNomFichierPDF = ThisWorkbook.Path & "\" & "Tableau.pdf"
   
   'Plages à tester à adapter pour les Feuilles 1 à 5
    Plage(0) = "A2:J10"
    Plage(1) = "A2:J10"
    Plage(2) = "A2:J10"
    Plage(3) = "A2:J10"
    Plage(4) = "A2:J10"

    Cpt = 0
    For i = 1 To ActiveWorkbook.Sheets.Count
        Set Wsh = ActiveWorkbook.Worksheets(i)
        If Application.CountA(Wsh.Range(Plage(i - 1))) > 0 Then
       'If Application.CountA(Wsh.Cells) > 0 Or Wsh.Shapes.Count > 0 Then
            ReDim Preserve Ar(Cpt)
            Ar(Cpt) = Sheets(i).Name
            Cpt = Cpt + 1
        End If
    Next i

    If Cpt = 0 Then
        MsgBox "Fichier PDF vide!"
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Sheets(Ar).Select

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

    Sheets(Feuil1.Name).Select
    Application.ScreenUpdating = True
End Sub

Option Explicit
Private Sub CommandButton2_Click()
Dim PrintDlg        As DialogSheet
Dim CurrentSheet    As Worksheet
Dim Sh              As Worksheet
Dim Cb              As CheckBox
Dim Sel_Sheets      As String
Dim FileName        As Variant
Dim TopPos          As Integer

Application.ScreenUpdating = False

    ' Check for protected workbook
    If ActiveWorkbook.ProtectStructure Then
        MsgBox "Le classeur est protégé.", vbCritical
        Exit Sub
    End If

    ' Add a temporary dialog sheet
    Set CurrentSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add

    ' Add the checkboxes
    TopPos = PrintDlg.Buttons(1).Top
    For Each Sh In ActiveWorkbook.Worksheets
        ' Skip hidden sheets
        If Sh.Visible Then
           PrintDlg.CheckBoxes.Add(78, TopPos, 100, 16.5).Text = Sh.Name
           TopPos = TopPos + 13
        End If
    Next
    
    ' Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = PrintDlg.CheckBoxes(1).Left + PrintDlg.CheckBoxes(1).Width
    
    ' Set dialog height, width, and caption
    With PrintDlg.DialogFrame
        .Height = Application.Max(68, PrintDlg.DialogFrame.Top + TopPos - 34)
        .Width = PrintDlg.Buttons(1).Left ' <-- bizarre mais fonctionne
        .Caption = "Cochez les feuilles à publier"
    End With
    
    ' Set focus on Cancel
    PrintDlg.Buttons(1).BringToFront
    
    ' Display the dialog box
    CurrentSheet.Activate
    Application.ScreenUpdating = True
        If PrintDlg.Show Then
            For Each Cb In PrintDlg.CheckBoxes
                If Cb.Value = xlOn Then Sel_Sheets = Trim(Sel_Sheets & " " & Cb.Caption)
            Next Cb
            If Sel_Sheets <> vbNullString Then
               ' Display the dialog box to obtain filename
                FileName = Application.GetSaveAsFilename( _
                    FileFilter:="Publication (*.pdf),*.pdf", _
                    InitialFileName:=ThisWorkbook.Path)
                If Not FileName = False Then
                    ' Select sheets to export
                    Sheets(Split(Sel_Sheets)).Select
                    ' Publication
                    ActiveSheet.ExportAsFixedFormat _
                        Type:=xlTypePDF, OpenAfterPublish:=True, _
                        FileName:=FileName
                End If
            End If
        End If

    ' Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False
    PrintDlg.Delete

' Reactivate original sheet
CurrentSheet.Activate
Set CurrentSheet = Nothing
Set PrintDlg = Nothing

End Sub