Launch VBE using Alt+F11. From menuInsertUserform. Place a combobox and
command button. Right click on UserForm1 iconView Code and paste the below
code in the code section. Press F5 to initiate the forma which will list down
all sheets in the combo box..Now you can have your code placed in command
button to pull information from the selected sheet..

Private Sub UserForm_Activate()
For Each shTemp In ThisWorkbook.Sheets
Me.ComboBox1.AddItem shTemp.Name
Next
Me.ComboBox1.ListIndex = 0
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"opieandy" wrote:

I have 20 sheets that all have a standard set of columns and rows. Each
sheet has the statistics of a baseball team in a standard format (Games,
At-Bats, Hits, etc., all exactly the same columns on each sheet.)

On Sheet 1 preceding these 20 sheets, I want the user to select the
desired team from a list and have Sheet 1 pull in the stats from the
applicable team's stat sheet. So my file looks like this:

Sheet 1 - Main Sheet for User Input and Presenting Desired Data
Sheet 2 - Atlanta Braves stats
Sheet 3 - Boston Red Sox stats
Sheet 4 - NY Yankees stats
Sheets 5-20... - more individual team stats like sheets 2-4

I have designed this in VBA using option buttons. The problem is that for
each button, I have to have a set of code that is exactly the same, with the
only difference being the sheet name that is being referred to.

This is inefficient because when I identify a change I want to make to the
code, I have to make it to all 20 code sections. Further, I am expanding
this file to include hundreds of sheets and don't to copy and paste code
every time, or have to change hundreds of sections for a single common edit I
make to the code.

I would like to have a single code section, with the sheet name as a
variable the user selects (from a drop-down list, set of buttons, whatever).

Let's say it's a drop-down list. The user selects "NY Yankees" from the
drop-down list. The VBA code goes to the NY Yankees sheet and pulls in a
range of data into Sheet 1.

How do I get VBA to allow for user input of a sheet so I can have 1 set of
code and variable sheet names that code can pull data from?

Thanks!

Chris