Você pode criar um formulário de usuário com um ComboBox preenchido com as pastas de trabalho abertas e WorkSheets. Visualize as escolhas e selecione uma.
Sub ViewListOfOpenWorkBooksAndWorkSheets()
Dim book As Workbook, sheet As Worksheet
For Each book In Workbooks
For Each sheet In book.Worksheets
UserForm1.ComboBox1.AddItem book.Name + "!" + sheet.Name
Next sheet
Next book
UserForm1.Show
End Sub