en fait j'ai truqué en faisant une formule sur chaque ligne qui renvoie 1 si le CA est compris dans la fourchette et 0 si ce n'est pas le cas. (j'ai créé une page sur laquelle on met la fourchette du CA souhaité)
ensuite j'ai créé un code pour macro qui trie le tableau dans chaque page pour n'afficher que les lignes qui ont le CA souhaité.
A savoir que j'ai un autre critère et donc j'ai deux formules. La macro trie donc sur deux colonnes.
C'est un peu bidon mais ça m'a permis déjà de biaisé. Je préférerais coller toutes les lignes concernées de chaque page dans une nouvelle pour une meilleure visibilité et que ça soit plus synthétique.
Le code est le suivant mais il en faudrait totalement un autre sur lequel je n'ai aucune idée :
Sub Macro5()
'
' Macro5 Macro
'
'
Sheets("région1").Select
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
Range("U5:V5").Select
Selection.AutoFill Destination:=Range("U5:V39")
Range("U5:V39").Select
Cells.Select
Range("P1").Activate
Selection.AutoFilter
Sheets("région2").Select
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
Range("U5:V5").Select
Selection.AutoFill Destination:=Range("U5:V47")
Range("U5:V47").Select
Cells.Select
Range("S1").Activate
Selection.AutoFilter
Sheets("région3").Select
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
Range("U5:V5").Select
Selection.AutoFill Destination:=Range("U5:V42")
Range("U5:V42").Select
Cells.Select
Range("P1").Activate
Selection.AutoFilter
Sheets("région4").Select
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
Range("U5:V5").Select
Selection.AutoFill Destination:=Range("U5:V12")
Range("U5:V12").Select
Cells.Select
Range("O1").Activate
Selection.AutoFilter
Sheets("région5").Select
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
Range("U5:V5").Select
Selection.AutoFill Destination:=Range("U5:V35")
Range("U5:V35").Select
Cells.Select
Range("P1").Activate
Selection.AutoFilter
Sheets("région6").Select
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
Range("U5:V5").Select
Selection.AutoFill Destination:=Range("U5:V29")
Range("U5:V29").Select
Cells.Select
Range("R1").Activate
Selection.AutoFilter
Sheets("région7").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
Range("U6:V6").Select
Selection.AutoFill Destination:=Range("U6:V40")
Range("U6:V40").Select
Cells.Select
Range("P1").Activate
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveSheet.ListObjects("Tableau1").Range.AutoFilter Field:=21, Criteria1:= _
"=1", Operator:=xlOr, Criteria2:="="
ActiveSheet.ListObjects("Tableau1").Range.AutoFilter Field:=22, Criteria1:= _
"=1", Operator:=xlOr, Criteria2:="="
Sheets("région6").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveSheet.Range("$A$1:$AC$29").AutoFilter Field:=21, Criteria1:="=1", _
Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$1:$AC$29").AutoFilter Field:=22, Criteria1:="=1", _
Operator:=xlOr, Criteria2:="="
Sheets("région5").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveSheet.Range("$A$1:$V$35").AutoFilter Field:=21, Criteria1:="=1", _
Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$1:$V$35").AutoFilter Field:=22, Criteria1:="="
Sheets("région4").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveSheet.Range("$A$1:$V$12").AutoFilter Field:=21, Criteria1:="=1", _
Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$1:$V$12").AutoFilter Field:=22, Criteria1:="="
Sheets("région3").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveSheet.Range("$A$1:$V$44").AutoFilter Field:=21, Criteria1:="=1", _
Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$1:$V$44").AutoFilter Field:=22, Criteria1:="="
Sheets("région2").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveSheet.Range("$A$1:$AD$47").AutoFilter Field:=21, Criteria1:="=1", _
Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$1:$AD$47").AutoFilter Field:=22, Criteria1:="=1", _
Operator:=xlOr, Criteria2:="="
Sheets("région1").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveSheet.Range("$A$1:$V$40").AutoFilter Field:=21, Criteria1:="=1", _
Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$1:$V$40").AutoFilter Field:=22, Criteria1:="=1", _
Operator:=xlOr, Criteria2:="="
Sheets("région7").Select
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Sheets("région6").Select
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Sheets("région5").Select
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Sheets("région4").Select
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Sheets("région3").Select
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Sheets("région2").Select
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Sheets("région1").Select
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
End Sub
merci