listbox column header macro

Hello friends,

I have a code that helps me to open a list box from where i can choose items arranged in column A, sheet2.
In column A, sheet2 i have: countries, cities, cars, fruits.
In Sheet1 i have 2 buttons, when clicking <Let's go button>, a list box is open and i'm able to choose items that are inserted in column H, sheet1.

Let's consider this items now are header columns, under Countries i have: UK, Albania, Egypt, Argentina, Canada, Mongolia, Tanzania.
The challange is how can i add in column H, sheet1, one after another, if i want to select in listbox Countries and Cars columns, sheet2?

Keeping in listbox just the header columns, when i choose one or more of them, after clicking OK button, on column H to get all items located under the selected header columns.

Attached is an example, but it works only for Header columns, not for what is under each header column.

Thank you

Hi tuspilica

If I understand the issue, create Dynamic Named Ranges for Countries, Cities, Cars, Planets and Fruits.
Then change your OK Button Code to this...

Please Login or Register  to view this content.

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

i tested the code, it's working perfect, but when i want to replace the name of the columns and run the macro, it fails.
i'm realy confused, is there any hidden predefined list with the coulmn heads?

Can you please take a minute to cast a look over this code again?
The error line is this:
Range(ListBox1.List(i)).Copy Sheet2.Range("H200").End(xlUp)(2, 1)

How come the code knows that i changed the name of the column head?
The data under the column head can be modified. in other words, i can not use it

Thanks

Hi tuspilica

Your Column Headers are in a Named Range and that Named Range is the Row Source for the UserForm ListBox1.

If you've changed those Column Head Names you also need to change these Names to correspond to the Column Head Names.

Dynamic Named Ranges for Countries, Cities, Cars, Planets and Fruits

Understand? Yes? No?

Hi tuspilica

Good for you...you "see" the relationship. Seeing relationships is a major part of developing in VBA.