Selectable ComboBox in VBA returning sheet names to move/hide

Question

First of all, you all are great in your knowledge and help. I've got a question about comboboxes as I know nothing about them.

Here is my scenario: I have a book with about 93 tabs, titled by an agent name. I need to manually filter out agents who are no longer here and copy the contents of the agent's sheet into another sheet.

The problem I'm having is how do I create a combo box with all of these sheet names that can select multiple entries? And how do I loop it to go through the list of tabs to get the list I need to move and/or hide.

Thanks a bunch from a truly confused VBA Noob....

Jeff

SeanC SeanC · Accepted Answer · 2014-06-26T18:54:31

Why create a combo box?

We may have to make another sheet to hold the names, but it can be done with 2 macros. One to create the list of sheets

Sub ListSheets()
Dim sh
Dim pos As Long
pos = 1 ' 1st line
For Each sh In ActiveWorkbook.Sheets
    ActiveSheet.range("A" & pos) = sh.Name
    pos = pos + 1
Next
End Sub

then you can delete the ones that you don't want to copy to leave a list of sheets that do need to be copied.

With that, you can then copy these remaining sheets into a new workbook

Sub CopySheets()
Dim sh
Dim pos As Long
pos = ActiveSheet.Range("A1").End(xlDown).Row

While pos > 0
    'remember to change the "Book1" into the name of whatever
    '  spreadsheet you have open to copy the data into.
    Sheets(Range("A" & pos)).Copy After: Workbooks("Book1").Sheets (1)
    pos = pos - 1
Wend

End Sub

Both of these macros can be attached to buttons, and with the use of InputBox you can even prompt for the name of the spreadsheet to copy in to.

Selectable ComboBox in VBA returning sheet names to move/hide

1 Answers