Não sei se compreendi corretamente, mas supondo que sua lista das abas estão na coluna A e na Coluna B tem uma lista com validação "Sim e Não" utilize a rotina abaixo.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sAba
    
    If Target.Value = "Sim" Then
        sAba = Target.Offset(0, -1).Value
        Worksheets(sAba).Select
    End If

End Sub

Obrigado Mauro, troquei, select por visible =xlsheetvisible e funcionou perfeitamente