Option Explicit
Sub test()
Dim a, b(), n As Long, i As Long, j As Long, txt As String
Dim dico As Object, ws As Worksheet
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
a = Sheets("CPP").Range("b1").CurrentRegion.Value
For i = 2 To UBound(a, 1)
txt = Join$(Array(a(i, 2), a(i, 1)), Chr(2))
n = n + 1
dico(txt) = n
Next
ReDim b(1 To dico.Count, 1 To UBound(a, 2) - 2)
For Each ws In Worksheets([{"SP1","SP2","SP3","SP4","SP5"}])
a = ws.Range("a1").CurrentRegion.Value
For i = 2 To UBound(a, 1)
txt = Join$(Array(a(i, 1), a(i, 2)), Chr(2))
If dico.exists(txt) Then
For j = 3 To UBound(a, 2)
b(dico(txt), j - 2) = a(i, j)
Next
End If
Next
Next
Application.ScreenUpdating = False
With Sheets("CPP").Range("b1")
.CurrentRegion.Offset(1, 2).ClearContents
.Offset(1, 2).Resize(UBound(b, 1), UBound(b, 2)).Value = b
End With
Set dico = Nothing
Application.ScreenUpdating = True
End Sub