Si tu as une astuce pour avoir toutes les feuilles non vides, au format pdf, je suis preneur...
Sub PDF()
Dim w As Worksheet, n%
For Each w In Worksheets
If Application.CountA(w.Cells) Then w.Select n = 0: n = n + 1
Next
ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\MonPDF"
End Sub
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
Lorsqu'on enregistre un fichier Excel au format pdf, on n'obtient que la feuille en cour dans le document final.
Si tu as une astuce pour avoir toutes les feuilles non vides, au format pdf, je suis preneur...
Sub PDF()
Dim w As Worksheet, n%
For Each w In Worksheets
If Application.CountA(w.Cells) Then w.Select n = 0: n = n + 1
Next
ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\MonPDF"
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
Ce qu'il faut bien comprendre c'est :J'ai juste du mal à m'expliquer le rôle de ce "n" ...
C'est normal mais CISCO veut peut-être parler de tableaux vides, il faudra juste adapter le test.perso quand je fais
activeworkbook.ExportAsFixedFormat.......
et qu'il y a des feuilles vides ,elle ne sont pas prises en compte