Private Sub Workbook_Open()
Dim chemin$, fichier$, source$, w As Worksheet
chemin = Me.Path & "\"
fichier = "Classeur A.xlsx"
source = "B16:B30"
For Each w In Me.Worksheets
If w.Name Like "CP#*" Then
w.Range(source).FormulaArray = "='" & chemin & "[" & fichier & "]" & w.Name & "'!" & source 'formule matricielle
w.Range(source) = w.Range(source).Value 'supprime les formules
w.Range(source).Replace 0, "", xlWhole 'efface les valeurs zéro
End If
Next
End Sub