Combobox et ouverture de feuilles

'En entête de module
Private Tab1(), Tab2(), Tab3(), Tab4()

Private Sub ComboBox1_Click()

Erase Tab1: ComboBox2.Clear

Dim Ws As Worksheet, cpt As Integer

For Each Ws In ThisWorkbook.Worksheets
    With Ws
        If CStr(.Range("B1")) = ComboBox1 Then
            ReDim Preserve Tab1(cpt)
            Tab1(cpt) = .Name
            cpt = cpt + 1
            Me.ComboBox2.Value = .Range("B3")
            If Me.ComboBox2.ListIndex = -1 Then Me.ComboBox2.AddItem .Range("B3")
        End If
    End With
Next
End Sub

Private Sub ComboBox2_Click()
Erase Tab2: ComboBox3.Clear

Dim Ws As Worksheet, cpt As Integer

For Each Ws In ThisWorkbook.Worksheets(Tab1)
    With Ws
        If CStr(.Range("B3")) = ComboBox2 Then
            ReDim Preserve Tab2(cpt)
            Tab2(cpt) = .Name
            cpt = cpt + 1
            Me.ComboBox3.Value = .Range("J1")
            If Me.ComboBox3.ListIndex = -1 Then Me.ComboBox3.AddItem .Range("J1")
        End If
    End With
Next
End Sub

Private Sub ComboBox3_Click()
Erase Tab3: ComboBox4.Clear

Dim Ws As Worksheet, cpt As Integer

For Each Ws In ThisWorkbook.Worksheets(Tab2)
    With Ws
        If CStr(.Range("J1")) = ComboBox3 Then
            ReDim Preserve Tab3(cpt)
            Tab3(cpt) = .Name
            cpt = cpt + 1
            Me.ComboBox4.Value = .Range("J2")
            If Me.ComboBox4.ListIndex = -1 Then Me.ComboBox4.AddItem .Range("J2")
        End If
    End With
Next
End Sub


Private Sub ComboBox4_Click()
Erase Tab4

Dim Ws As Worksheet, cpt As Integer

For Each Ws In ThisWorkbook.Worksheets(Tab3)
    With Ws
        If CStr(.Range("J2")) = ComboBox4 Then
            ReDim Preserve Tab4(cpt)
            Tab4(cpt) = .Name
            cpt = cpt + 1
        End If
    End With
Next
'ICI, la variable Tab4 contient les noms des feuilles ou on trouve les 4 valeurs
For cpt = LBound(Tab4) To UBound(Tab4)
    MsgBox Tab4(cpt)
Next cpt
End Sub