Private Sub CommandButton1_Click() 'bouton Transfert
Dim mes As Byte
mes = MsgBox("Le transfert supprimera des données du tableau source." _
& vbLf & "Voulez-vous mémoriser les données ?", 3, "Mémorisation")
If mes = 2 Then Exit Sub
If mes = 6 Then ThisWorkbook.Names.Add "Memo", [B2:AF11].Value 'nom défini
Transfert [A21:A23], [B2:AF11], [B21:AF23]
End Sub
Private Sub CommandButton2_Click() 'bouton Rétablir
Application.ScreenUpdating = False
If Not IsError([Memo]) Then [B2:AF11] = [Memo]
[B2:AF11].Replace "#N/A", "" 'cellules vides
[B21:AF23].ClearContents 'RAZ zone de destination
End Sub
Sub Transfert(ref As Range, source As Range, destination As Range)
Dim t1, t2, ub&, lig&, col%, i As Variant
t1 = source 'matrice, plus rapide
t2 = destination
ub = UBound(t1, 2)
For lig = 1 To UBound(t1)
For col = 1 To ub
i = Application.Match(t1(lig, col), ref, 0)
If IsNumeric(i) Then
t2(i, col) = t1(lig, col)
t1(lig, col) = ""
End If
Next
Next
source = t1
destination = t2
End Sub