Sub Macro1()
Dim tableau() As Variant
nombre_ligne_max_par_feuille = 25
nombre_ligne_totale = Range("A65000").End(xlUp).Row
'RAZ tous les sauts de page
ActiveSheet.ResetAllPageBreaks
'cacher/compter les lignes, ajouter les sauts de page
saut_page = 7 'saut initial
nb_saut = 1
n = 1
ReDim Preserve tableau(1 To 2, 1 To n)
'remplir un tableau contenant le nombre de ligne des tableaux
'cacher les lignes inutiles
For i = 7 To nombre_ligne_totale
If Cells(i, 1).Value = "" And Cells(i, 3).Value = "" Then
Rows(i).EntireRow.Hidden = True
Else
If Left(Cells(i, 3), 8) <> "Tableau " Then
tableau(1, n) = tableau(1, n) + 1
derniere_ligne = i
End If
End If
If Left(Cells(i, 3), 8) = "Tableau " Then
n = n + 1
ReDim Preserve tableau(1 To 2, 1 To n)
tableau(2, n - 1) = derniere_ligne
End If
Next i
For i = 1 To UBound(tableau, 2)
nb_ligne = nb_ligne + tableau(1, i)
If nb_ligne > nombre_ligne_max_par_feuille Then
ActiveWindow.View = xlPageBreakPreview
nb_saut = nb_saut + 1
If i = UBound(tableau, 2) Then
'ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A" & derniere_ligne)
Else
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A" & tableau(2, i) + 1)
nb_ligne = 0
ActiveWindow.View = xlNormalView
End If
End If
Next i
End Sub
Private Sub CommandButton1_Click()
Application.ScreenUpdating = 0
Rows("11:16").EntireRow.Hidden = True
Rows("32:33").EntireRow.Hidden = True
Rows("46:48").EntireRow.Hidden = True
Rows("71:71").EntireRow.Hidden = True
Rows("86:86").EntireRow.Hidden = True
End Sub
Private Sub CommandButton2_Click()
Application.ScreenUpdating = 0
Rows("6:100").EntireRow.Hidden = False
End Sub
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.ScreenUpdating = 0
Range("A6:G99").Select
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub