inserire dato creato col click in celle consecutive

Dim rDest   As Long     'riga destinazione
rDest = Sheets("Archivio").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Archivio").Range("A" & rDest) = "0"     '<== aggiornare il numero per ogni macro

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rDest   As Long
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("L3:N14", "M2")) Is Nothing Then
        rDest = Sheets("Archivio").Cells(Rows.Count, "A").End(xlUp).Row + 1
        Sheets("Archivio").Range("A" & rDest) = Target.Value
        Select Case Target
            Case 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
                Range("U" & 3 + Target).Value = Range("U" & 3 + Target).Value + 1
            Case 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24
                Range("W" & Target - 9).Value = Range("W" & Target - 9).Value + 1
            Case 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36
                Range("Y" & Target - 21).Value = Range("Y" & Target - 21).Value + 1
        End Select
        Application.EnableEvents = False
        Range("L2").Select
        Application.EnableEvents = True
    End If
End Sub

Option Explicit
Sub Cerca_Terni()
    Dim terni(11) As String
    Dim ur     As Long
    Dim rig    As Long                            'coordinata riga terni
    Dim col    As Long                            'coordinata colonna terni
    Dim estr   As Long                            'numeri estratti
    Dim x, y, z, a, b                             'contatori array + righe, colonne
    Dim sez    As Long                            'zone terni (Colpi)
    Dim comb   As String                          'combinazione terni
    Dim cerca  As String                          'combinazione terni cercati
    Dim ws1    As Worksheet
    Dim ws2    As Worksheet
    Set ws1 = Sheets("Metodo")
    Set ws2 = Sheets("Archivio")
    'costruzione array dei terni: B7:D10 + B12:D15 + G7:I10
    x = 0
    y = 7: z = 10
    a = 2: b = 4
    For sez = 1 To 3
        If sez = 2 Then
            y = 12: z = 15
        End If
        If sez = 3 Then
            y = 7: z = 10
            a = 7: b = 9
        End If
        For rig = y To z
            For col = a To b
                comb = comb & Cells(rig, col)
            Next col
            terni(x) = comb
            x = x + 1
            comb = ""
        Next rig
        comb = ""
    Next sez
    'confronto i terni con gli estratti
    With ws2
        ur = .Range("A" & Rows.Count).End(xlUp).Row
        For x = 0 To 11
            For estr = 2 To ur - 2                'fino alla terzultima
                'con ciclo confronto l'estratto + 2 offset
                cerca = .Range("A" & estr) & .Range("A" & estr).Offset(1, 0) & .Range("A" & estr).Offset(2, 0)
                If terni(x) = cerca Then
                    'aumento colpi terni
                    With ws1
                        Select Case x
                            Case Is = 0, 1, 2, 3
                                .Range("E" & x + 7) = .Range("E" & x + 7) + 1
                            Case Is = 4, 5, 6, 7
                                .Range("E" & x + 8) = .Range("E" & x + 8) + 1
                            Case Is = 8, 9, 10, 11
                                .Range("J" & x - 1) = .Range("J" & x - 1) + 1
                        End Select
                    End With
                End If
            Next estr
        Next x
    End With
End Sub