drop down menu containing worksheet names

hi all,

i'd love it if someone could show off their excel prowess on this one.

what i'm trying to do is have a workbook with multiple "common"
worksheets and one main worksheet. on the main worksheet i want to
have a drop-down menu where you select which common worksheet you want
to pull data from to populate the main worksheet.

the common worksheets will always be the exact same structure and will
contain a weeks worth of data. every week i want to "move and copy" a
common worksheet so that i have one worksheet for evey week. i don't
want to have to make a million worksheets at the inception of the
workbook.

what i think will be the tricky part is having the drop-down menu
automatically update so at any point in time i would be able to select
any common worksheet available.

ready to learn...

-jarrad

Sounds like you should be looking at the INDIRECT function in HELP.
HTH,
Gary Brown

"J-Rad" wrote in message
ups.com...
hi all,

i'd love it if someone could show off their excel prowess on this one.

what i'm trying to do is have a workbook with multiple "common"
worksheets and one main worksheet. on the main worksheet i want to
have a drop-down menu where you select which common worksheet you want
to pull data from to populate the main worksheet.

the common worksheets will always be the exact same structure and will
contain a weeks worth of data. every week i want to "move and copy" a
common worksheet so that i have one worksheet for evey week. i don't
want to have to make a million worksheets at the inception of the
workbook.

what i think will be the tricky part is having the drop-down menu
automatically update so at any point in time i would be able to select
any common worksheet available.

ready to learn...

-jarrad

Add a combobox from the controls toolbar, and add this code to it

Private Sub ComboBox1_DropButtonClick()
Dim sh
With Me.ComboBox1
.Clear
For Each sh In ThisWorkbook.Sheets
.AddItem sh.Name
Next sh
End With
End Sub

You can use its click event and do your stuff there.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Brown" wrote in message
...
Sounds like you should be looking at the INDIRECT function in HELP.
HTH,
Gary Brown

"J-Rad" wrote in message
ups.com...
hi all,

i'd love it if someone could show off their excel prowess on this one.

what i'm trying to do is have a workbook with multiple "common"
worksheets and one main worksheet. on the main worksheet i want to
have a drop-down menu where you select which common worksheet you want
to pull data from to populate the main worksheet.

the common worksheets will always be the exact same structure and will
contain a weeks worth of data. every week i want to "move and copy" a
common worksheet so that i have one worksheet for evey week. i don't
want to have to make a million worksheets at the inception of the
workbook.

what i think will be the tricky part is having the drop-down menu
automatically update so at any point in time i would be able to select
any common worksheet available.

ready to learn...

-jarrad

Bob-

thanks so much...that's exactly what i needed.

my project is going to be perfect now.

thanks a million

-jarrad

Bob Phillips wrote:
Add a combobox from the controls toolbar, and add this code to it

Private Sub ComboBox1_DropButtonClick()
Dim sh
With Me.ComboBox1
.Clear
For Each sh In ThisWorkbook.Sheets
.AddItem sh.Name
Next sh
End With
End Sub

You can use its click event and do your stuff there.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Brown" wrote in message
...
Sounds like you should be looking at the INDIRECT function in HELP.
HTH,
Gary Brown

"J-Rad" wrote in message
ups.com...
hi all,

i'd love it if someone could show off their excel prowess on this
one.

what i'm trying to do is have a workbook with multiple "common"
worksheets and one main worksheet. on the main worksheet i want
to
have a drop-down menu where you select which common worksheet you
want
to pull data from to populate the main worksheet.

the common worksheets will always be the exact same structure and
will
contain a weeks worth of data. every week i want to "move and
copy" a
common worksheet so that i have one worksheet for evey week. i
don't
want to have to make a million worksheets at the inception of the
workbook.

what i think will be the tricky part is having the drop-down menu
automatically update so at any point in time i would be able to
select
any common worksheet available.

ready to learn...

-jarrad