JK
Look in the macro I gave you. Find "G5". Change it to "B1". I don't know
what you mean when you ask about the "other sheets". The idea here, as I
understand it, is to click on a name in the drop-down and have that sheet
pop up on the screen. You don't need any macro placed in any of those
"other sheets" to make this happen. I think you and I need to do this via
email. Send me your file or a sample of it. Fake the data as you wish. I
need just the layout. I'll look at what you have and we'll go from there.
My email is
(E-Mail Removed). Remove the "extra" from this
email. Otto
"JK" <(E-Mail Removed)> wrote in message
news:637B1D65-310D-4D12-96BD-(E-Mail Removed)...
> Otto,
>
> I am almost there (I think). I have to back up a second though. Instead of
> cell "G5", could I make the drop down in "B1". That is where I would like
> it.
> I made a drop down too but I do not think I did it right because when I
> clicked on the persons name, nothing changed. I think I need a really
> dumbed
> down explanation! Also, for the other sheets do I need to put that macro
> in
> too?
>
> "Otto Moehrbach" wrote:
>
>> JK
>> Sorry about that. I should have told you more. Right-click the sheet
>> tab
>> of the sheet that holds the drop-down. A little menu pops up. Click on
>> View Code. A blank code module appears. Paste this macro into that
>> module.
>> "X" out of the module to return to your sheet. Now when you make a
>> selection from the drop-down (it must be in G5), the sheet you selected
>> will
>> become the active sheet (it will appear on the screen). Read the other
>> options I discussed in my previous post and come back if you want to
>> pursue
>> any of them Otto
>>
>> "JK" <(E-Mail Removed)> wrote in message
>> news:427906FD-C659-41E8-A9F2-(E-Mail Removed)...
>> > Otto,
>> >
>> > Sorry if I sound stupid, but I do not even know where to start with
>> > doing
>> > a
>> > macro. I clicked on Macro and it asked me for the macro name, which I
>> > don't
>> > know. If you have time. Could you give me a little step-by-step?
>> >
>> > "Otto Moehrbach" wrote:
>> >
>> >> JK
>> >> This little macro will do it. Note that this includes nothing to
>> >> prevent anyone from selecting any sheet they wish to view. I simply
>> >> placed
>> >> a drop-down in G5 that displays the names of all the rep sheets. You
>> >> select
>> >> one of the sheet names and that sheet is selected and becomes the
>> >> active
>> >> sheet. There is a lot more that you can have in this kind of a setup.
>> >> For
>> >> instance, you can have the code to always display the sheet with the
>> >> drop-down when the file is first opened. You can also add additional
>> >> code
>> >> to have the sheets hidden until the code is fired. You can even have
>> >> the
>> >> sheets VeryHidden (requires VBA to unhide). You can also add a
>> >> password
>> >> requirement to access a sheet.
>> >> About combining workbooks, yes, VBA can combine data from them into
>> >> one
>> >> workbook. I don't know what you would use the drop-down for doing
>> >> that
>> >> except to maybe select an individual workbook to combine to the
>> >> master.
>> >> What you can do is have all the desired workbooks and the master in
>> >> one
>> >> folder and fire the code and combine them all. Is that what you mean?
>> >> Otto
>> >> Private Sub Worksheet_Change(ByVal Target As Range)
>> >> If Target.Count > 1 Then Exit Sub
>> >> If Not Intersect(Target, Range("G5")) Is Nothing Then
>> >> Sheets(Range("G5").Value).Select
>> >> End If
>> >> End Sub
>> >>
>> >>
>> >> "JK" <(E-Mail Removed)> wrote in message
>> >> news:09C431B4-F0A1-402A-96FD-(E-Mail Removed)...
>> >> > Otto,
>> >> >
>> >> > Thanks for responding. I have one workbook and each of my sales reps
>> >> > enter
>> >> > information on his sheet. Can you tell me how to do this? That would
>> >> > be
>> >> > great! Also, if I did have numerous workbooks, could I combine them
>> >> > into
>> >> > one
>> >> > with a convenient drop-down box?
>> >> >
>> >> > "Otto Moehrbach" wrote:
>> >> >
>> >> >> JK
>> >> >> I'm not sure what you have. Do you have numerous workbooks,
>> >> >> one
>> >> >> for
>> >> >> each sales rep, and you want to combine them all into one, or do
>> >> >> you
>> >> >> have
>> >> >> only one workbook and each sales rep inputs information to only his
>> >> >> sheet?
>> >> >> What you say at the end can certainly be done, that is, click on a
>> >> >> name
>> >> >> in a
>> >> >> drop-down and that sheet displays. HTH Otto
>> >> >>
>> >> >> "JK" <(E-Mail Removed)> wrote in message
>> >> >> news:CFD18EB1-B1B0-42C2-ACE5-(E-Mail Removed)...
>> >> >> > Hello,
>> >> >> >
>> >> >> > I was hoping someone could help me. I have a "master" template
>> >> >> > set
>> >> >> > up
>> >> >> > and
>> >> >> > each of my sales reps goes in and fills out the required
>> >> >> > information. I
>> >> >> > was
>> >> >> > wondering if there was a way to "combine" all of them into the
>> >> >> > master,
>> >> >> > so
>> >> >> > all
>> >> >> > I would have to do to look through each persons sheet is click on
>> >> >> > a
>> >> >> > drop
>> >> >> > down
>> >> >> > box on the master sheet, click on the person's name, and all of
>> >> >> > their
>> >> >> > information would come up. Any help will be appreciated. Thanks.
>> >> >>
>> >> >> .
>> >> >>
>> >> .
>> >>
>> .
>>
Similar Threads
