One method you can use is a table on a separate worksheet. I liked your idea of a worksheet name lookup, so that's what the table allows you to do. For more flexibility, you can set a value of Yes or No below each recipient. Here is a screenshot:
The first column of the table lists all of the Sheet names. If you rename a tab, be sure to update the table.
Each column to the right of of the Sheet names is a recipient. This could be a single person or a group of people, it doesn't matter. The headings are just a reminder to you, so that you can set the row values to Yes or No as needed.
The yellow cell has Data Validation, which only allows you to select one of the recipients. In the image below, you can see how that is set up:
The green Copy button is assigned to the macro that does the selections. Now, here is the code:
Dim CurrentWorkbook As Workbook
Dim NewWorkbook As Workbook
Dim LookupTable As ListObject
Dim i As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set CurrentWorkbook = ThisWorkbook
Set NewWorkbook = ActiveWorkbook
Set LookupTable = ActiveSheet.ListObjects("Table1")
For i = 1 To .DataBodyRange.Rows.Count
' Uncomment to see what's going on
'Debug.Print i, .DataBodyRange.Rows(i), "Sheet "; LookupTable.DataBodyRange(i, 1)
If .DataBodyRange.Rows(i) = "Yes" Then
Sheets(LookupTable.DataBodyRange(i, 1).Value).Copy After:=NewWorkbook.Sheets(NewWorkbook.Sheets.Count)
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
If all goes well, you will have a new, unsaved workbook with just the sheets relevant to the chosen recipient. Depending on your Excel settings, you may have 1 or more blank sheets (the default is 3). You can delete them before saving your new workbook.
You will want to add error-handling to ensure that ScreenUpdating and DisplayAlerts are set to TRUE if something goes wrong.