Selection from List Box copies to separate Workbook

[Vamphyri]

Good Day!

I am trying to create some programming that would copy a Named Range from one workbook to another based on a selection within a drop down list box.

I have searched and tried several things. None of which have produced any results worth mentioning.

Any pointers, hints, or tricks would be greatly appreciated.




In the immortal words of Socrates, who said:
"I drank what?

 

[ThomasLafferty]

Hi Vamphyri! Could you give a little more detail of exactly what you would like to copy to the other workbook? Also, could you explain a bit more about the contents of your dropdown list and its type (Data validation, combobox etc.)

The code you will need depends on what you are copying (cell contents, range address, range name itself...) and what kind of dropdown is making the selection.

Could you clarify a bit?

Tom

Born once die twice; born twice die once.

 

[Vamphyri]

TL,

Sorry for not being clearer in my OP.

I have 2 workbooks:
- Proposal
- Plan Designs

Plan Designs contains approx. 25 worksheets each with between 5 - 20 specific details eg.

A B C
1 Coinsurance 20% 30%
2 Out of Pocket $2,500 $3,000
3 Emergency $50 each visit $150 each visit

The above example may have a range name of "Plan_1"

Proposal is blank except for the entries in column "A" which remain unchanged and static.

I am attempting to use combo boxes or list boxes on the PROPOSAL workbook to call (copy) the entire Named Range from the PLAN DESIGNS workbook to the PROPOSAL workbook.

Suggestions as to which kind of drop down would be appropriate would also be appreciated.

Please let me know if I need to further clarify.

Thanks tons


In the immortal words of Socrates, who said:
"I drank what?

 

[ThomasLafferty]

If I read you correctly, you are interested in bringing over all values and formatting from the various named ranges. A possible issue to deal with is whether or not to link the pasted values or just bring over the values (rather than a formulaic link).

Also, it sounds like the named ranges may vary in number, so here's what I would recommend:
1. Loop through the named ranges in the Plan Designs book.
2. After each iteration through the loop, add the name encountered as a row item in an unbound combobox.
3. Set the AfterUpdate event of your combobox to copy and paste the data from the Plan Designs book.

If this sounds like what you would like to accomplish, let me know and I can help with coding if you need it. In the meantime, here are a few potential issues:

1. You will need to determine if you will always paste the data in the same spot, or if you want to prompt the user.
2. You will need to plan for the possibility that all names could potentially get deleted from the Plan Designs book (probably won't happen, but it could). If there are no names, you will need an error trap.
3. You will need to decide if you want to link the data, or just paste the values.

Hope this gets you started in the right direction.

Tom

Born once die twice; born twice die once.

 

[Vamphyri]

Tom,

Thank you for the tips.

I figured out how to Loop through all sheets in the book and create the named ranges. I also included some code to delete all range names before the rest of the code executes. That way I have the most up-to-date info.

As for adding the range names to an unbound combo box, I am a little lost. What I would like to do is have one combo box look up the sheet names and, if "sheet 2" is selected, have a second combo box be populated with teh range names from "sheet 2".

I think this is possible, but I'll be dipped if I know how.

Any advise would be greatly appreciated.

Chris


PS: Only 138 shopping days til Christmas.


In the immortal words of Socrates, who said:
"I drank what?

 

[ThomasLafferty]

Look at using Data Validation with the second one using Indirect as its source. There's a good article on this site if you Google it. If you still need help, let me know and I will try after lunch.
Tom

Born once die twice; born twice die once.

 

[Vamphyri]

Tom,

I think I have the combo box problem almost licked.

One last question. This one may need to be posted as a new thread. If so, I apologize in advance.

Once a user selects from the 1st DD box this will populate the 2nd DD box. OK so far.

After the user chooses an entry from the 2nd DD box, I need to copy the values from the source workbook (opened via code) and pasted to the active workbook.

Any ideas or articles you can point me to?

Hope you had a good lunch. It's the 2nd meal of the day you know.

Chris


In the immortal words of Socrates, who said:
"I drank what?