copy/paste table using VBA

[Dnk_83]

Morning! I have a workbook that has numerous sheets and each sheet has a table on it. The purpose of the workbook is to standardise naming convention for products, so each sheet is the family of products and the table in each sheet is the name of the product with columns for all the 'options' of the products as below:

Product name	Option 1	Option 2	Option 3	Option 4	Option 5	Option 6	Option 7
Product 1	Option 1a	Option 2a	Option 3a		Option 5a	Option 6a	Option 7a
	Option 1b	Option 2b	Option 3b				Option 7b
							Option 7c
Product 2	Option 1a	Option 2a	Option 3a		Option 5a	Option 6a	Option 7a
	Option 1b
Product 3	Option 1a	Option 2a	Option 3a	Option 4a	Option 5a	Option 6a	Option 7a
	Option 1b	Option 2b	Option 3b		Option 5b		Option 7b
		Option 2c
		Option 2d
		Option 2e
		Option 2f

the idea is to have dependent drop downs where you select the 'family' (i.e. worksheet name) and then product name (column A of the 'family' worksheet) and then option 1 dropdown, option 2 dropdown etc.

I already have VBA coding in place so that each time the sheet is opened a list is created of each sheet name ('Master' sheet, col.A) and the corresponding table on that sheet ('Master' sheet, col.B) so that more sheets/tables can be added.

The issue that I am having is that I am not sure how to create the drop down lists that are dependent on the previous selection? e.g. If you select Product 1 in the first dropdown, then how would you generate the drop downs of the options JUST for product 1? Let me know if anyone needs more information! And i am happy to email a generic workbook (product name removed for privacy!) if it is needed. Thanks in advance!

 

[Akuini]

The issue that I am having is that I am not sure how to create the drop down lists that are dependent on the previous selection? e.g. If you select Product 1 in the first dropdown, then how would you generate the drop downs of the options JUST for product 1?

1. What about option2, does it depends on option1? and option3 depends on option 2? etc?
2. Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

 

[Dnk_83]

1. What about option2, does it depends on option1? and option3 depends on option 2? etc?
2. Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

1. Some of the options are unique to the product, the options are only dependent on the product, not the other options.
2. link: Naming Convention - product descriptions.xlsm

 

[Akuini]

I've downloaded your file.
I'm confused, you have sheets family 1 to family 4, do you want to have dropdown on each sheets from column Option 1 to the last column?
What is the source of the dropdown? All I can see is all sheets already have some data in it.
Can you give an example what you're trying to do, step by step?

 

[Dnk_83]

I've downloaded your file.
I'm confused, you have sheets family 1 to family 4, do you want to have dropdown on each sheets from column Option 1 to the last column?
What is the source of the dropdown? All I can see is all sheets already have some data in it.
Can you give an example what you're trying to do, step by step?

Better idea! This is the link for what i managed to put together: Naming Convention - product descriptions (my attempt).xlsm

It is messy as hell (FYI the blue columns would all be hidden) and the ONLY way i can get it to work is to ensure each option has the product name on the same line so it pulls all the data through.

Ideally I am wanting it to be cleaner by using VBA more and to have it future proofed so that if lines are added to the tables or sheets are added to the workbook i don't need to re-write formulas to include them!

 

[Dnk_83]

I've downloaded your file.
I'm confused, you have sheets family 1 to family 4, do you want to have dropdown on each sheets from column Option 1 to the last column?
What is the source of the dropdown? All I can see is all sheets already have some data in it.
Can you give an example what you're trying to do, step by step?

Hi Akuini, did what i supply help with the sort of thing that i am looking for?