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


"opieandy" wrote:

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?