VBA - Allow User to Select Which Worksheet to Copy

[strax]

I have an excel doc with multiple worksheets, which will have different names every time it's used. I want to create a macro that will have a pop-up of all tabs (was trying to use the CommandBars("worksbook tabs")), the user can then select which tabs they would like to copy. I see that I can easily copy a worksheet if the name is known/pre-defined, but how do you have the name be selected from a pop-up/list/user input as the name will vary?

 

[johnnyL]

I actually recently did this via the commandBars. It allows the user to select a sheet from a list. Maybe it will assist you.

Let us know:

Sub ChooseSheetNumberToUse()                                                                        ' select a sheet to copy
'
    Dim UserSelectedSheet       As String
'
    Worksheets(1).Activate                                                                          ' Had to add this to allow the sheet selected to work
'
    MsgBox "In the next popup window, Click the sheet name " & vbCrLf & "that you would like to copy." ' Advise user what to do next
'
    If Application.CommandBars("workbook tabs").Controls(16).Caption Like "More Sheets*" Then       ' This CommandBars only shows up to 16 sheets, after that it
'                                                                                                       will display the first 15 sheets and the 16th item will be
'                                                                                                       an option of "More Sheets..." which will give full list
        Application.CommandBars("workbook tabs").Controls(16).Execute                               ' If more than 16 sheets are found, click the "More Sheets ..."
    Else                                                                                            ' Otherwise ...
        Application.CommandBars("Workbook Tabs").ShowPopup 800, 300                                 '   Display the sheets found
    End If
'
    UserSelectedSheet = ActiveSheet.Name                                                            ' Save the sheet name that the user selected
    Debug.Print UserSelectedSheet                                                                   ' Test print, for verification, name of user selected sheet
'
'   The variable 'UserSelectedSheet' now contains the sheet name selected by the user
'
'
'   Add desired additional coding here ...
'
End Sub

 

[strax]

I actually recently did this via the commandBars. It allows the user to select a sheet from a list. Maybe it will assist you.

Let us know:

Sub ChooseSheetNumberToUse()                                                                        ' select a sheet to copy
'
    Dim UserSelectedSheet       As String
'
    Worksheets(1).Activate                                                                          ' Had to add this to allow the sheet selected to work
'
    MsgBox "In the next popup window, Click the sheet name " & vbCrLf & "that you would like to copy." ' Advise user what to do next
'
    If Application.CommandBars("workbook tabs").Controls(16).Caption Like "More Sheets*" Then       ' This CommandBars only shows up to 16 sheets, after that it
'                                                                                                       will display the first 15 sheets and the 16th item will be
'                                                                                                       an option of "More Sheets..." which will give full list
        Application.CommandBars("workbook tabs").Controls(16).Execute                               ' If more than 16 sheets are found, click the "More Sheets ..."
    Else                                                                                            ' Otherwise ...
        Application.CommandBars("Workbook Tabs").ShowPopup 800, 300                                 '   Display the sheets found
    End If
'
    UserSelectedSheet = ActiveSheet.Name                                                            ' Save the sheet name that the user selected
    Debug.Print UserSelectedSheet                                                                   ' Test print, for verification, name of user selected sheet
'
'   The variable 'UserSelectedSheet' now contains the sheet name selected by the user
'
'
'   Add desired additional coding here ...
'
End Sub

Thank you for the code. I don't see how this copies the selected worksheet, I only see that it makes that sheet active once selected. Would the next line be something like Sheets("UserSelectedSheet").Copy?

 

[strax]

Thank you for the code. I don't see how this copies the selected worksheet, I only see that it makes that sheet active once selected. Would the next line be something like Sheets("UserSelectedSheet").Copy?

Nevermind, I figured it out. Had to other lines that needed to be removed from the old code. Thank you!

 

[johnnyL]

Thank you for the code. I don't see how this copies the selected worksheet, I only see that it makes that sheet active once selected. Would the next line be something like Sheets("UserSelectedSheet").Copy?

Correct. The code I posted for you was just for selecting the sheet, like the title of this thread asked for.

Glad you were able to put it to use.

 

[johnnyL]

Would the next line be something like Sheets("UserSelectedSheet").Copy?

No quotations, just Sheets(UserSelectedSheet).copy

 

[Want2BExcel]

I actually recently did this via the commandBars. It allows the user to select a sheet from a list. Maybe it will assist you.

Let us know:

Sub ChooseSheetNumberToUse()                                                                        ' select a sheet to copy
'
    Dim UserSelectedSheet       As String
'
    Worksheets(1).Activate                                                                          ' Had to add this to allow the sheet selected to work
'
    MsgBox "In the next popup window, Click the sheet name " & vbCrLf & "that you would like to copy." ' Advise user what to do next
'
    If Application.CommandBars("workbook tabs").Controls(16).Caption Like "More Sheets*" Then       ' This CommandBars only shows up to 16 sheets, after that it
'                                                                                                       will display the first 15 sheets and the 16th item will be
'                                                                                                       an option of "More Sheets..." which will give full list
        Application.CommandBars("workbook tabs").Controls(16).Execute                               ' If more than 16 sheets are found, click the "More Sheets ..."
    Else                                                                                            ' Otherwise ...
        Application.CommandBars("Workbook Tabs").ShowPopup 800, 300                                 '   Display the sheets found
    End If
'
    UserSelectedSheet = ActiveSheet.Name                                                            ' Save the sheet name that the user selected
    Debug.Print UserSelectedSheet                                                                   ' Test print, for verification, name of user selected sheet
'
'   The variable 'UserSelectedSheet' now contains the sheet name selected by the user
'
'
'   Add desired additional coding here ...
'
End Sub

Hi JohnnyL,
I like this Almost what I need...including the last comment, Sheets(UserSelectedSheet).copy.
Is it possible to choose multiple sheets and not only one from Application.CommandBars("Workbook Tabs").ShowPopup 800, 300? And then save them all to a new workbook? The new workbook should stay active. So it easy to save it immediately to a path.
....well it could be cool to write that path in the code also....along with filename based on a fixed string and value in two cells B1 and E1

 

[Want2BExcel]

I actually recently did this via the commandBars. It allows the user to select a sheet from a list. Maybe it will assist you.

Let us know:

Sub ChooseSheetNumberToUse()                                                                        ' select a sheet to copy
'
    Dim UserSelectedSheet       As String
'
    Worksheets(1).Activate                                                                          ' Had to add this to allow the sheet selected to work
'
    MsgBox "In the next popup window, Click the sheet name " & vbCrLf & "that you would like to copy." ' Advise user what to do next
'
    If Application.CommandBars("workbook tabs").Controls(16).Caption Like "More Sheets*" Then       ' This CommandBars only shows up to 16 sheets, after that it
'                                                                                                       will display the first 15 sheets and the 16th item will be
'                                                                                                       an option of "More Sheets..." which will give full list
        Application.CommandBars("workbook tabs").Controls(16).Execute                               ' If more than 16 sheets are found, click the "More Sheets ..."
    Else                                                                                            ' Otherwise ...
        Application.CommandBars("Workbook Tabs").ShowPopup 800, 300                                 '   Display the sheets found
    End If
'
    UserSelectedSheet = ActiveSheet.Name                                                            ' Save the sheet name that the user selected
    Debug.Print UserSelectedSheet                                                                   ' Test print, for verification, name of user selected sheet
'
'   The variable 'UserSelectedSheet' now contains the sheet name selected by the user
'
'
'   Add desired additional coding here ...
'
End Sub

I made a new post with this question, so you could get some acknowledgement for the new answer

VBA let user choose multiple sheets in workbook from list and copy to new workbook

I found this answer from JohnnyL in this post VBA - Allow User to Select Which Worksheet to Copy Its's almost what I need 🤌 Sub ChooseSheetNumberToUse() ' select a sheet to copy ' Dim UserSelectedSheet As String '...

www.mrexcel.com