Combine Multiple Worksheets into One with a Drop Down Box

[JK]

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.

 

[Otto Moehrbach]

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]

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]

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]

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]

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]

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]

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 address removed). Remove the "extra" from this
email. Otto