drop down menu containing worksheet names

[J-Rad]

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

 

[Gary Brown]

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

 

[Bob Phillips]

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)

 

[J-Rad]

Bob-

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

my project is going to be perfect now.

thanks a million

-jarrad