VBA - duplicate sheets based on drop-down list in another worksheet

[MossyPants]

Hi all, just starting out with VBA and I'm having trouble piecing this one together.

I have a worksheet called "chooser" that contains 4 drop-down lists in cells A1-A4. The drop-down lists contain numbers 0-10.

I would like to duplicate some hidden worksheets (named "apples", "oranges", etc) based on the selection in the drop-down list in "chooser".

For example, if user selects "3" from drop-down list in A1, I would like to duplicate hidden worksheet called "apples" 3 times and name them "apples1","apples 2", and "apples3".

If user selects "1" from drop-down list in A2, I would like to duplicate worksheet called "oranges" and name it "oranges2".

Hope this question is clear. Any help is greatly appreciated!

 

[My Aswer Is This]

You said:
4 drop-down lists in cells A1-A4.

And you explained what A1 and A2 does so what is A3 and A4 for?

 

[MossyPants]

You said:
4 drop-down lists in cells A1-A4.

And you explained what A1 and A2 does so what is A3 and A4 for?

They are for other hidden sheets, let's say "bananas" and "plums". So if user selects 2 in drop-down in A3, two "bananas" worksheets are duplicated and named "bananas1" and "bananas2".

 

[My Aswer Is This]

When you say drop down list I assume you mean Data Validation list.

So I would think you would have all the sheet names in A1's DataValidation list
And the number of copies you want in A2

So if you select "Alpha" from the DataValidation in Range("A1") the script would look in Range("A2") for the number of copies to make.

This way we only need one Datavalidation list in A1
And just enter a number in Range("A2") of how many copies you want

 

[MossyPants]

Here is a visual. This is what I currently have:
Worksheet "chooser" with 4 drop-down data validation lists, each one with numbers 0-10.

Here is what I want:
Based on selection in drop-down lists, hidden worksheets become duplicated and renamed with the appropriate number.

 

[My Aswer Is This]

Here is a visual. This is what I currently have:
Worksheet "chooser" with 4 drop-down data validation lists, each one with numbers 0-10.
View attachment 65208

Here is what I want:
Based on selection in drop-down lists, hidden worksheets become duplicated and renamed with the appropriate number.
View attachment 65209

OK and where is the sheet name.

I choose 8 in A3 how do I know the sheet name?
Is the sheet named in B3

 

[MossyPants]

OK and where is the sheet name.

I choose 8 in A3 how do I know the sheet name?
Is the sheet named in B3

The sheet name is hard-coded into an IF statement in the VBA code. Anytime a non-0 number is selected from A3, that would result in "bananas" sheet being duplicated, renamed, and unhidden.

 

[My Aswer Is This]

The sheet name is hard-coded into an IF statement in the VBA code. Anytime a non-0 number is selected from A3, that would result in "bananas" sheet being duplicated, renamed, and unhidden.

This will have to be a sheet cell change event. script
So when a change is made to range A1 or A2 Or A3 or A4
The script would run doing it the way you want.
So I'm going to let someone else help you.
I will keep watching through.

 

[Peter_SSs]

Welcome to the MrExcel board!

More questions:
What should happen if ..

1. In A1 4 is chosen so we get 4 'apples' sheets 'apples1', 'apples2', 'apples3' and 'apples4' and then the drop-down in A1 is used again and 6 is now chosen?
2. In A1 4 is chosen so we get 4 'apples' sheets 'apples1', 'apples2', 'apples3' and 'apples4' and then the drop-down in A1 is used again and 2 is now chosen?

 

[MossyPants]

Welcome to the MrExcel board!

More questions:
What should happen if ..

1. In A1 4 is chosen so we get 4 'apples' sheets 'apples1', 'apples2', 'apples3' and 'apples4' and then the drop-down in A1 is used again and 6 is now chosen?
2. In A1 4 is chosen so we get 4 'apples' sheets 'apples1', 'apples2', 'apples3' and 'apples4' and then the drop-down in A1 is used again and 2 is now chosen?

Hm, valid points I had not considered.
Would a work-around be for the macro to be activated by a button, so that once the user has made their selection, they click a button, and the appropriate sheets are created?
If they, then, change any of the numbers in the drop-down lists, and click the button again, all the visible worksheets are deleted, and everything is duplicated anew.