A force de chercher et d'essayer de nouveau code j'ai enfin trouvé la solution par moi même.
Je la diffuse au cas ou quelqu'un serait intéressé.
Sub essai2()
Dim v As Integer
For i = 4 To Sheets.Count
If InStr(Sheets(i).Name, "OCS20") <> 0 Then
Sheets("BILAN OCS20").Select
v = 1
While (Cells(v, 1).Value <> "")
v = v + 1
Wend
Cells(v, 1).Value = Sheets(i).Name
Sheets("BILAN OCS20").Cells(v, 2).Value = Sheets(i).Range("B17").Value
Sheets("BILAN OCS20").Cells(v, 3).Value = Sheets(i).Range("B18").Value
Sheets("BILAN OCS20").Cells(v, 4).Value = Sheets(i).Range("B19").Value
Sheets("BILAN OCS20").Cells(v, 5).Value = Sheets(i).Range("B20").Value
Sheets("BILAN OCS20").Cells(v, 6).Value = Sheets(i).Range("B21").Value
Sheets("BILAN OCS20").Cells(v, 7).Value = Sheets(i).Range("B22").Value
Sheets("BILAN OCS20").Cells(v, 8).Value = Sheets(i).Range("B23").Value
Sheets("BILAN OCS20").Cells(v, 9).Value = Sheets(i).Range("B24").Value
Sheets("BILAN OCS20").Cells(v, 10).Value = Sheets(i).Range("B25").Value
Sheets("BILAN OCS20").Cells(v, 11).Value = Sheets(i).Range("B26").Value
Sheets("BILAN OCS20").Cells(v, 12).Value = Sheets(i).Range("B29").Value
Sheets("BILAN OCS20").Cells(v, 13).Value = Sheets(i).Range("B30").Value
Sheets("BILAN OCS20").Cells(v, 14).Value = Sheets(i).Range("B31").Value
Sheets("BILAN OCS20").Cells(v, 15).Value = Sheets(i).Range("C43").Value
Sheets("BILAN OCS20").Cells(v, 16).Value = Sheets(i).Range("D43").Value
Sheets("BILAN OCS20").Cells(v, 17).Value = Sheets(i).Range("E43").Value
Sheets("BILAN OCS20").Cells(v, 18).Value = Mid(Sheets(i).Range("A13").Value, 35)
End If
If InStr(Sheets(i).Name, "OCS120") <> 0 Then
Sheets("BILAN OCS120").Select
v = 1
While (Cells(v, 1).Value <> "")
v = v + 1
Wend
Cells(v, 1).Value = Sheets(i).Name
Sheets("BILAN OCS120").Cells(v, 2).Value = Sheets(i).Range("B17").Value
Sheets("BILAN OCS120").Cells(v, 3).Value = Sheets(i).Range("B18").Value
Sheets("BILAN OCS120").Cells(v, 4).Value = Sheets(i).Range("B19").Value
Sheets("BILAN OCS120").Cells(v, 5).Value = Sheets(i).Range("B20").Value
Sheets("BILAN OCS120").Cells(v, 6).Value = Sheets(i).Range("B21").Value
Sheets("BILAN OCS120").Cells(v, 7).Value = Sheets(i).Range("B22").Value
Sheets("BILAN OCS120").Cells(v, 8).Value = Sheets(i).Range("B23").Value
Sheets("BILAN OCS120").Cells(v, 9).Value = Sheets(i).Range("B24").Value
Sheets("BILAN OCS120").Cells(v, 10).Value = Sheets(i).Range("B25").Value
Sheets("BILAN OCS120").Cells(v, 11).Value = Sheets(i).Range("B26").Value
Sheets("BILAN OCS120").Cells(v, 12).Value = Sheets(i).Range("B27").Value
Sheets("BILAN OCS120").Cells(v, 13).Value = Sheets(i).Range("B28").Value
Sheets("BILAN OCS120").Cells(v, 14).Value = Sheets(i).Range("B31").Value
Sheets("BILAN OCS120").Cells(v, 15).Value = Sheets(i).Range("B32").Value
Sheets("BILAN OCS120").Cells(v, 16).Value = Sheets(i).Range("B33").Value
Sheets("BILAN OCS120").Cells(v, 17).Value = Sheets(i).Range("C47").Value
Sheets("BILAN OCS120").Cells(v, 18).Value = Sheets(i).Range("D47").Value
Sheets("BILAN OCS120").Cells(v, 19).Value = Sheets(i).Range("E47").Value
Sheets("BILAN OCS120").Cells(v, 20).Value = Mid(Sheets(i).Range("A13").Value, 35)
End If
Next i
End Sub