Boucle alleger le code

bonjour

esi il possible svp de reduire ce code suivant :
Sub nnnn()

 Dim a, b(4) As Integer, i As Integer, f As Integer, m As Integer, L As Integer, dlig As Integer

Columns("H:M").ClearContents

a = Worksheets("NEW_VB_config").Range("o2:o12") 'nom des 11 feuilles
For i = 2 To 100
'For L = 1 To 6
 For f = 1 To 11                 'boucle sur les feuilles
If a(f, 1) <> "" Then
dlig = Range("H65536").End(xlUp).Row + 1
  With Worksheets(a(f, 1))
    If .Range("ao" & i).Value <> "" Then
       x = Left(.Range("ao" & i), 1)
        y = Left(.Range("ao" & i), 2)
    y1 = Right(y, 1)
     y2 = Left(.Range("ao" & i), 3)
    y22 = Right(y2, 1)
    y3 = Right(.Range("ao" & i), 1)
      'For m = 1 To 4
       'b(m) = Mid(.Range("ao" & i), m, 1)
       
      
        
          'tab1
          ' Q
          If ActiveCell.Activate And .Range("a" & i).Value = ActiveSheet.Range("a1") Then
                 If ActiveCell.Row = 2 And ActiveCell.Column = 2 And x = 1 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                         
                  ElseIf ActiveCell.Row = 3 And ActiveCell.Column = 2 And x = 2 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                         
                  ElseIf ActiveCell.Row = 4 And ActiveCell.Column = 2 And x = 3 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                         
                  ElseIf ActiveCell.Row = 5 And ActiveCell.Column = 2 And x = 4 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)

        'C
                  ElseIf ActiveCell.Row = 2 And ActiveCell.Column = 3 And y1 = 1 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 3 And ActiveCell.Column = 3 And y1 = 2 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 4 And ActiveCell.Column = 3 And y1 = 3 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 5 And ActiveCell.Column = 3 And y1 = 4 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                
         'D
                  ElseIf ActiveCell.Row = 2 And ActiveCell.Column = 4 And y22 = 1 Then
                        ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                         
                  ElseIf ActiveCell.Row = 3 And ActiveCell.Column = 4 And y22 = 2 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 4 And ActiveCell.Column = 4 And y22 = 3 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 5 And ActiveCell.Column = 4 And y22 = 4 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                    
         'P
                  ElseIf ActiveCell.Row = 2 And ActiveCell.Column = 5 And y3 = 1 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 3 And ActiveCell.Column = 5 And y3 = 2 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 4 And ActiveCell.Column = 5 And y3 = 3 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 5 And ActiveCell.Column = 5 And y3 = 4 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                End If
          End If
          
          
          'tab2
          'Q
          If ActiveCell.Activate And .Range("a" & i).Value = ActiveSheet.Range("a7").Value Then
                 If ActiveCell.Row = 8 And ActiveCell.Column = 2 And x = 1 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                         
                  ElseIf ActiveCell.Row = 9 And ActiveCell.Column = 2 And x = 2 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                         
                  ElseIf ActiveCell.Row = 10 And ActiveCell.Column = 2 And x = 3 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                         
                  ElseIf ActiveCell.Row = 11 And ActiveCell.Column = 2 And x = 4 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)

        'C
                  ElseIf ActiveCell.Row = 8 And ActiveCell.Column = 3 And y1 = 1 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 9 And ActiveCell.Column = 3 And y1 = 2 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 10 And ActiveCell.Column = 3 And y1 = 3 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 11 And ActiveCell.Column = 3 And y1 = 4 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                
         'D
                  ElseIf ActiveCell.Row = 8 And ActiveCell.Column = 4 And y22 = 1 Then
                        ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                         
                  ElseIf ActiveCell.Row = 9 And ActiveCell.Column = 4 And y22 = 2 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 10 And ActiveCell.Column = 4 And y22 = 3 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 11 And ActiveCell.Column = 4 And y22 = 4 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                    
         'P
                  ElseIf ActiveCell.Row = 8 And ActiveCell.Column = 5 And y3 = 1 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 9 And ActiveCell.Column = 5 And y3 = 2 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 10 And ActiveCell.Column = 5 And y3 = 3 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 11 And ActiveCell.Column = 5 And y3 = 4 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                End If
          End If

 'tab3
          'Q
          If ActiveCell.Activate And .Range("a" & i).Value = ActiveSheet.Range("a13").Value Then
                 If ActiveCell.Row = 14 And ActiveCell.Column = 2 And x = 1 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                         
                  ElseIf ActiveCell.Row = 15 And ActiveCell.Column = 2 And x = 2 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                         
                  ElseIf ActiveCell.Row = 16 And ActiveCell.Column = 2 And x = 3 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                         
                  ElseIf ActiveCell.Row = 17 And ActiveCell.Column = 2 And x = 4 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)

        'C
                  ElseIf ActiveCell.Row = 14 And ActiveCell.Column = 3 And y1 = 1 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 15 And ActiveCell.Column = 3 And y1 = 2 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 16 And ActiveCell.Column = 3 And y1 = 3 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 17 And ActiveCell.Column = 3 And y1 = 4 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                
         'D
                  ElseIf ActiveCell.Row = 14 And ActiveCell.Column = 4 And y22 = 1 Then
                        ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                         
                  ElseIf ActiveCell.Row = 15 And ActiveCell.Column = 4 And y22 = 2 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 16 And ActiveCell.Column = 4 And y22 = 3 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 17 And ActiveCell.Column = 4 And y22 = 4 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                    
         'P
                  ElseIf ActiveCell.Row = 14 And ActiveCell.Column = 5 And y3 = 1 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 15 And ActiveCell.Column = 5 And y3 = 2 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 16 And ActiveCell.Column = 5 And y3 = 3 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                  ElseIf ActiveCell.Row = 17 And ActiveCell.Column = 5 And y3 = 4 Then
                         ActiveSheet.Cells(dlig, 8) = .Cells(i, 1)
                         ActiveSheet.Cells(dlig, 9) = .Cells(i, 2)
                         ActiveSheet.Cells(dlig, 10) = .Cells(i, 3)
                         ActiveSheet.Cells(dlig, 11) = .Cells(i, 4)
                         ActiveSheet.Cells(dlig, 12) = .Cells(i, 5)
                         ActiveSheet.Cells(dlig, 13) = .Cells(i, 6)
                End If
          End If
          
          
           
           

     ' Next m
    End If
  End With
End If
 Next f
'Next L
Next i



End Sub

Bonjour
Vu comme ça on ne peut pas répondre. On ne sait même pas de quoi traite le code.
Expliquez plus et joindre un fichier exemple.
A+

bonjour

jai reussi a reduire le code

en faite le code recupere des activites dans mes bases donnes en fonction du nom de l’activites (colonne A) et en fonction de la colonne AO (en fct de premeir, 2eme 3eme 4eme chifre )

je vous remercie

Ce sujet a été automatiquement fermé après 30 jours. Aucune réponse n’est permise dorénavant.