Let User Select Sheet to Pull Data From

[opieandy]

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

 

[Jacob Skaria]

Launch VBE using Alt+F11. From menu>Insert>Userform. Place a combobox and
command button. Right click on UserForm1 icon>View 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

 

[opieandy]

Jacob, thanks, I have followed your instructions. How do I link the Command
Button to the Combo Box? In the Command Button, where I would normally have
"Sheets("2").Select", do I just delete those lines of code because the
Command Button is going to automatically go to the sheet selected in the
Combo Box?

My code goes back and forth between Sheet 1 and Sheet 2 to draw in different
sections of data so I don't overwrite some things that are in Sheet 1. How
do I bring in the data from Sheet 2 in several different steps? For example,
Step 1 is to copy in Columns B-D, and Step 2 is to copy in Columns G-H
(omitting Columns E-F because those Columns in Sheet 1 can't be overwritten)?

 

[opieandy]

And this is a silly question but I've never created the Combo Box using F11
before. How do I get the Combo Box onto Sheet 1?

 

[opieandy]

I figured out how to bring up the Userform by using a command button.

I still need help understanding how to link the Control Box drop-down sheet
selected to the code that pulls in the data from the selected sheet.

Whenever I select an item from the Combo Box, it reverts to the first sheet.
It won't actually select the sheet that I am choosing.

 

[Gary Keramidas]

you can try to adapt this. i used a combobox named drop down 1. change to
suit your worksheet.
then right click the combobox and assign the 2nd macro
run the first macro to clear and populate the list box.

then selecting the sheet from the drown should take you to the sheet.

Sub Fill_DropDown()
Dim dDown As Object
Set dDown = Worksheets("Sheet1").DropDowns("Drop Down 1")
dDown.RemoveAllItems

For i = 1 To Worksheets.Count
dDown.AddItem Worksheets(i).Name
Next
End Sub



Sub Select_Sheet()
Dim dDown As Object
Set dDown = Worksheets("Sheet1").DropDowns("Drop Down 1")
Worksheets(dDown).Select
End Sub

 

[Gary Keramidas]

do you want a userform or a combobox on sheet 1?

 

[Gary Keramidas]

make yourself a userform with 1 combobox on it
right click the form in the project explorer window
paste this code and then run the form

Private Sub ComboBox1_Change()
Worksheets(ComboBox1.Value).Select
End Sub

Private Sub UserForm_Initialize()
Dim i As Long

For i = 1 To Worksheets.Count
With Me.ComboBox1
.AddItem Worksheets(i).Name
End With
Next
End Sub