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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> Sounds like you should be looking at the INDIRECT function in HELP.
> HTH,
> Gary Brown
>
> "J-Rad" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > Sounds like you should be looking at the INDIRECT function in HELP.
> > HTH,
> > Gary Brown
> >
> > "J-Rad" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >