Private Sub CommandButton1_Click() 'bouton Copier
Dim f As String, derlig As Long, derlig1 As Long
f = "'" & ThisWorkbook.Path & "\[SOFT.xlsx]PFE'!"
derlig = Val(CStr(ExecuteExcel4Macro("MATCH(1E99," & f & "C5)"))) 'dernier nombre
derlig1 = Val(CStr(ExecuteExcel4Macro("MATCH(""zzzz""," & f & "C5)"))) 'dernier texte
derlig = IIf(derlig > derlig1, derlig, derlig1)
If derlig = 0 Then derlig = 2: GoTo 1 'si le tableau est vide
With Range("E3:E" & derlig)
.FormulaArray = "=" & f & "E3:E" & derlig
.Value = .Value 'supprime la formule
End With
1 Range("E" & derlig + 1 & ":E" & Rows.Count) = "" 'RAZ sous le tableau
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub