Sub PrépaMenus()
Dim WSh As Worksheet
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Extraction logiciel (*.xlsx, *.xls)", "*.xlsx;*.xls"
.InitialFileName = ThisWorkbook.Path & "\"
If .Show = -1 Then WBkPath = .SelectedItems(1) Else Exit Sub
End With
Set WSh = Application.Workbooks.Open(WBkPath, , True).Worksheets(1)
RàZ_Menus
With WSh
Derlgn = .Cells(.Rows.Count, 1).End(xlUp).Row
tb = .Cells(1, 1).Resize(Derlgn, 5).Formula
.Cells(1, 1).Resize(Derlgn, 5).FormulaLocal = tb
tb = .Cells(1, 1).Resize(Derlgn, 5).Formula
End With
WSh.Parent.Close savechanges:=False
Set Cible = Sh_Menus.Cells(5, 1).Resize(1, 5)
NbLgn = UBound(tb, 1)
début = 1: i = 2
While i <= NbLgn
Do While i <= NbLgn
If (Not IsNumeric(tb(i, 1)) Or IsEmpty(tb(i, 1))) Then
i = i + 1
Else
Exit Do
End If
Loop
fin = i
If fin <= NbLgn + 1 Then
ReDim temp(1 To 16, 1 To 5)
k = 1
For L = début To fin - 1
For j = 1 To 5
temp(k, j) = tb(L, j)
Next
k = k + 1
If k = 5 Or k = 8 Or k = 11 Or k = 14 Then
For j = 1 To 5
temp(k, j) = Replace(String(9, "x"), "x", ChrW(8213))
Next
k = k + 1
End If
Next
End If
Cible.Resize(16).Value = temp
nbpages = nbpages + 1
Set Cible = Cible.Offset(23)
début = fin
i = début + 1
Wend
Application.Goto Sh_Menus.Cells(1, 1)
Application.ScreenUpdating = True
With Sh_Menus
.PrintOut preview:=True, from:=1, To:=nbpages
End With
End Sub
Sub RàZ_Menus()
Dim PlageRàZ As Range
Set PlageRàZ = Sh_Menus.[A5:E23]
For i = 1 To 7
PlageRàZ.ClearContents
Set PlageRàZ = PlageRàZ.Offset(23)
Next
End Sub