1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
|
Sub Test_Impression()
'impression d'un groupe GR de fiche
' Application.ScreenUpdating = False
With Sheets("MENU")
Select Case .Range("G19") ' CHARGE LA valeur à RECHECHER DANS RECHERCHE sur BASE colonne K
Case "TOUT"
'call impressionTOUT
'MsgBox .Range("G19")
Case Is <> ""
RechercheEtImpressionGroupe .Range("G19"), Sheets("BASE"), Sheets("FICHES")
.Range("G19") = ""
Case ""
MsgBox "Aucune saisie n'a été faite !", vbCritical, "Impression d'un groupe GR"
Exit Sub
End Select
End With
' Application.ScreenUpdating = True
End Sub
Sub RechercheEtImpressionGroupe(ByVal ChaineARechercher As String, ByVal ShBase As Worksheet, ByVal ShFiches As Worksheet)
Dim AireRecherche As Range
Dim DerniereLigne As Long, I As Long
Dim ColNom As Long, ColPrenom As Long, ColSexe As Long, ColAge As Long, ColPoids As Long, ColTaille As Long, ColEtab As Long, ColGr As Long
With ShBase
ColNom = 3: ColPrenom = 4: ColSexe = 5: ColAge = 6: ColPoids = 8: ColTaille = 9: ColEtab = 10: ColGr = 11
DerniereLigne = .Range("C1000").End(xlUp).Row
If DerniereLigne < 3 Then Exit Sub
Set AireRecherche = .Range(.Cells(3, ColGr), .Cells(DerniereLigne, ColGr))
For I = 1 To AireRecherche.Count
If AireRecherche(I) = ChaineARechercher Then
With ShFiches
.Range("D6") = AireRecherche(I).Offset(0, ColNom - ColGr)
.Range("G6") = AireRecherche(I).Offset(0, ColPrenom - ColGr)
.Range("D8") = AireRecherche(I).Offset(0, ColSexe - ColGr)
.Range("G8") = AireRecherche(I).Offset(0, ColAge - ColGr)
.Range("D10") = AireRecherche(I).Offset(0, ColPoids - ColGr)
.Range("G10") = AireRecherche(I).Offset(0, ColTaille - ColGr)
.Range("D15") = AireRecherche(I).Offset(0, ColEtab - ColGr)
.Range("G15") = AireRecherche(I)
.PageSetup.PrintArea = "B3:I37" 'zone impression
ThisWorkbook.PrintPreview
End With
End If
Next I
Set AireRecherche = Nothing
End With
End Sub |
Partager