Best Way to Pull Data from Other Sheets

[opieandy]

I have 20 sheets of data that are formatted similarly. On the first sheet,
I want the user to select one of the 20 sheets and have Excel pull in a range
of data from that sheet.

What is the best way to allow for user input to select the sheet?

 

[Shane Devenshire]

Hi,

You can use a lot of these types of formulas:

=INDIRECT("'"&A1&"'!A9")

Where the sheet name is in A1 and the data you are pulling in is in A9.

This technique will only work if all spreadsheet are identical. Otherwise
you will need to consider VBA programming.

 

[Billy Liddel]

I just knocked this up with three sheets; two with tables

Sheet1 I called query

I created range names on the other sheets and called them Table1, Table2 ...
The tables are dynamic so each table may have a different number of rows.

Table1 refers to
=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,4)
in the Insert Name form, each table needs a name. Mine is a four column
table. and the headers are ignored (not copied) if you want the headers
remove the -1 after the Counta function

I created a list of the named ranges and used this for data validation in
Cell F1 in sheet1 (the sheet to copy to)

Select you table name in F1 then run the macro.

The code is copied into a VB Module. ALT + F11, Insert Module Paste the code
and return to the sheet

Sub CopyTable()
Dim tbl As Range
Dim cell As Range
Dim LastRow As Long
Dim LastCol As Integer

' Clear the last details
LastRow = Range("A1").CurrentRegion.Rows.Count
LastCol = Range("A1").CurrentRegion.Columns.Count
Range("A2:" & Cells(LastRow, LastCol).Address).ClearContents

Set cell = Range("F1")

Select Case cell
Case Is = "Table1"
Set tbl = Range("Table1")
tbl.Copy Range("A2")
Case Is = "Table2"
Set tbl = Range("Table2")
tbl.Copy Range("A2")
End Select

End Sub

You will need to have a 'Case Is = ' for each table and the code will need
to be modified.

When your are happy you can use a command button torun the code. View,
Toolbars, Forms to show the toolbar. click the 4th icon (Button) and draw
this in the sheet; you will be promted to assign a macro - choose CopyTable.
Change the button text to something informative.

To run change the text in F1 and click the button.

If this helps remember to click yes!

Regards
Peter

 

[Billy Liddel]

Shane thats why I went for the vb option therer was not a lot of info to go on.

 

[opieandy]

I'm going to give a more specific example here.

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 the main sheet preceding these 20 sheets, I want the user to select the
desired team from a list and have the main sheet pull in the stats from the
applicable team's stat sheet.

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 the main user sheet.

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

 

[Billy Liddel]

Chris
Sorry for the delay. I'm sticking with the Data Validation but have changed
the code.

If you intend to increase the number of sheets over time you need a macro to
update your list of sheets. This can be done on a hidden sheet. I placed mine
behind the MAIN sheet so its index is 2. I have assumed that the sheet will
be named after its team so the user will recognize the team.

Place this code in a general module

Sub ListSheetNames()
' Create on 2nd sheet then hide sheet
Dim i As Integer
Dim row As Integer

If Worksheets(2).Visible = False Then
Worksheets(2).Visible = True
End If

Worksheets(2).Select

For i = 3 To Sheets.Count
Cells(i - 1, 1) = Worksheets(i).Name 'Places list in column j change to
suit
Next i

Worksheets(2).Visible = False
Sheets("Main").Select

End Sub

You will need a dynamic range for your list so in the Insert name tabs
create a range name Teams and in the Refers to text box place this formula

=OFFSET(TeamsList!$A$2,0,0,COUNTA(TeamsList!$A:$A)-1) and click the add box.

If you place the cursor in the formula the range is selected, if it look OK
close the box. (you will have to unhide the sheet to see it)

The code for to copy the list goes in the same general module

Sub PullData()
Dim wks As Worksheet, Table As Range
Dim cell As String

cell = Range("L1") 'Change to suit
With Sheets(cell)
Set Table = .Range("A1:K50") 'Change range to suit
End With

Range("A1:K50").Value = Table.Value

End Sub

I set data validation in L1 In the Allow box choose list and in the Source
Type =Teams

The folling code is a Sheet Change event to call ranges and works when the
cell L1 changes.

Right click the MAIN sheet and select View code and copy the following code
into the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("L1")
PullData
End Sub

You may have to change the ranges
HTH

Please click Yes if this helps.

Peter

 

[Harlan Grove]

I have 20 sheets of data that are formatted similarly.   On the first sheet,
I want the user to select one of the 20 sheets and have Excel pull in a range
of data from that sheet.  

What is the best way to allow for user input to select the sheet?

With 29 or fewer such worksheets, there's a way to do this that avoids
volatile functions like INDIRECT. Something along the lines of

'Sheet 0'!B3:
=CHOOSE(MATCH(SheetNameEntry,{"Sheet 1";"Sheet 2"; . . . ;"Sheet 20"},
0),
'Sheet 1'!B3,'Sheet 2'!B3,'Sheet 3'!B3,'Sheet 4'!B3,'Sheet 5'!B3,
'Sheet 6'!B3,'Sheet 7'!B3,'Sheet 8'!B3,'Sheet 9'!B3,'Sheet 10'!B3,
'Sheet 11'!B3,'Sheet 12'!B3,'Sheet 13'!B3,'Sheet 14'!B3,'Sheet 15'!B3,
'Sheet 16'!B3,'Sheet 17'!B3,'Sheet 18'!B3,'Sheet 19'!B3,'Sheet 20'!B3)