A second workbook/worksheet that populates a drop down list

[uby]

Hi!

I have 2 workbooks(WB1 and WB2). The second workbook has a dropdown list that has names. The user selects a name on the list and then populates the appropriate information about that name that the user selected on the lower part of that worksheet. All of this information is on the first workbook.

How do I get the selected name's information on the bottom part of the second workbook.

Please help!!!

Uby

 

[TonyJollans]

Hi Uby,

Can you give a bit more detail please? For example ..

... What do you mean by 'the bottom part' of a worksheet/workbook?
... What sort of dropdown list are you using (cell validation, Control toolbox, Forms toolbar)? And how is it populated?
... How is the first workbook populated - in code or manually by the user?
... Under what circumstamces do you want the data in the second workbook - in response to user input or auomatically when the first workbook is populated? And will both workbooks be open?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[uby]

Tony,

No problem!

The bottom part means.... This is a template that requires some data from the first workbook that is funded by the user manually.

I have populated it by getting the information from the first workbook, copy it into a worksheet in the second workbook and the list box looks at that worksheet for the information that is to populate that list box.


I have a worksheet for the data that I need from the first workbook. Once all the information is in the second workbook, close the first workbook.

I hope this answers your questions.

Your help is appreciated.

Uby

 

[TonyJollans]

Hi Uby,

Sorry, but I am still struggling to understand this.

You have a dropdown in WB2 which is populated from a range of cells in the same sheet in WB2, which values also exist in WB1 along with a lot of related data.

A user selects an item from the dropdown and ... what? Does a cell automatically get populated from the selection? And if so, how is it populated - is it a fixed cell linked to the dropdown or do you have some code which decides what to do? Does anything else get automatically populated (or is this what you're asking for?) as a result of the selection? Does the user then enter more data?

If I understand at all, you want to lookup the selection from the dropdown in a table in WB1 and copy some data from WB1 to WB2.

Sorry it's just more questions. If you have any existing code it would help if you could post it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[uby]

Sorry Tony!

Here is the code as it stands now!

'Open workbook1
Workbooks.Open Filename:="C:\workbook1.xls", _
UpdateLinks:=0

'Pull the fund names from workbook1
Do Until Cells(intFundName, 3).Value = "Totals"
intFundName = intFundName + 1
Loop

'To not include the word "Totals" in that column
intFundName = intFundName - 1

Range("C5" & ":C" & intFundName).Select
Selection.copy
Windows("workbook2.xls").Activate
Sheets("FundInfo").Select
Range("B" & FindNextRowFundName).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Re-Open workbook1
Windows("workbook1.xls").Activate

'Pull the fund ids from workbook1
Do Until Cells(intFundID, 2).Value = ""
intFundID = intFundID + 1
Loop

Range("B5" & ":B" & intFundID).Select
Selection.copy

Windows("workbook2.xls").Activate
Sheets("FundInfo").Select
Range("C" & FindNextRowFundID).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Re-Open workbook1
Windows("workbook1.xls").Activate

'Pull the months from workbook1
Range("D4:O4").Select
Selection.copy
Windows("workbook2.xls").Activate
Sheets("Month Reference").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

See.... I am working with this pre-existing file that currently works with VLookups. This company wants me to completely automate this with a Macro.

The second workbook is a template. The first workbook is funded manually by the user. I just need to populate the second workbook with the information from the first workbook.

Now.... I am upto the part where in the dropdown list are the fund names that I have already populated, as you can see above. I basically have the FundInfo sheet that consist of the Fund Names that I have brought over from workbook1. I need for when the user clicks on a particular fund name that the corresponding information for that fund name populates the sheet that is in workbook2, names Class B.

Nevermind what I had said before about populating the bottom part of the template. That I know how to do.

I just need to figure out when the user has selected a fund name, for the Class B sheet to be populated with the selected fund name.

I hope this explanation is better.

Uby X-)

 

[TonyJollans]

Hi Uby,

Sorry for the delay. I am looking at your code now. It seems to copy from columns C and B in WB1 to columns B and C respectively in WB2, then copy (some of) row 4 in WB1 to row 3 in WB2, and that's it. We can tidy that code up a little but first of all ..

The copied data is source for the dropdown and you want to get data based on the selection. If you set up a cell linked to the dropdown you can use that a basis for a lookup in your data. Exactly how (by value or position) depends on which type of dropdown you are using.

I have to confess that I don't understand why you want to comvert something which already 'works with VLookups'. Are you adding functionality or something?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.