VBA loop through multiple sheets and select next item in dropdownlist

[CassieL]

Hi everyone,

I am new to macro and I couldn't find anything how I want it online. Please help.

I will have several tabs in a workbook, and each tabs has a dropdown list (data validation ) in cell C3,
I want to macro to go to each tab to select the next item in the drop down list

For example:
I have dropdown list that contains a,b,c,d,e,f,g...
In my sheet 1, I have "a" selected in C3 (dropdown list),
macro will go to sheet 2 to select "b" which is the next item after "a"
sheet 3 to select "c".... and so on.

How can I do this, please help! Thanks in advance

 

[Tetra201]

A couple of questions:
1) Are the lists identical on all the sheets?
2) If you select the last item in the list on Sheet 1, what should appear on Sheet 2?

 

[CassieL]

Hi Tetra,

Yes, all the list are identical. If I have last item in the Sheet 1, the loop will stop.

Here is what I have so far, I have a macro run copies based on #number of times I select,

Sub Copy_Tabs()Dim x As Integer
x = InputBox("Enter number of times to copy 4075 Wilson")
For numtimes = 1 To x
ActiveWorkbook.Sheets("4075 Wilson").Copy _
After:=ActiveWorkbook.Sheets("4075 Wilson")
Next
End Sub

C3 is the cell where contains the data validation and B2 I have following code to select the next item :

=INDEX(Key!$B$2:$B$57,IF(MATCH(H3,Key!$B$2:$B$57,0)+1>ROW(Key!$B$57)-ROW(Key!$B$2),Key!$B$2,MATCH(H3,Key!$B$2:$B$57,0)+1))

How can I combine both?

Thank you so much!

 

[Tetra201]

That’s somewhat confusing. Let’s try to untangle:

- You have a workbook where among other tabs you have a sheet named “4075 Wilson”.
- On that sheet in cell C3, you have a drop-down list (Data Validation).
- The items of the list are defined in cells B2:B57 on another sheet, named “Key”.
- You have a macro that makes several copies of the sheet “4075 Wilson” and places them immediately after it.
- The number of the copies varies; you enter it every time you run the macro.

Goal: You want each of the created copies to have the next successive item from the list in cell C3.
Example: if “4075 Wilson” has the 1st list item selected, “4075 Wilson (2)” should have 2nd, “4075 Wilson (3)” should have 3rd and so on.

It this description correct? If no, please modify as needed.

 

[CassieL]

Hi Tetra!

YES! I'm sorry if its confusing but you got it all right! Is the macro can do the work? Thanks again!!

 

[Tetra201]

CassieL,

Try the sub shown below. You can put it in the same module along with your current one.

Also, the formula in cell B2 does not look right to me.

Sub Copy_Tabs_New()
    Dim x, ListItem As Variant
    Dim numtimes As Byte
    x = InputBox("Enter number of times to copy 4075 Wilson")
    If Not IsNumeric(x) Then Exit Sub
    If (x < 1) Or (x > 55) Then Exit Sub
    ActiveWorkbook.Sheets("4075 Wilson").Select
    ListItem = Range("C3").Value
    Application.ScreenUpdating = False
    For numtimes = 1 To x
        ActiveWorkbook.Sheets("4075 Wilson").Copy After:=ActiveSheet
        Range("H3").Value = ListItem
        Range("C3").Value = Range("B2").Value
        ListItem = Range("C3").Value
    Next
    ActiveWorkbook.Sheets("4075 Wilson").Select
    Application.ScreenUpdating = True
End Sub

 

[CassieL]

I can't thank you enough Tetra 201! I learn so much from you! By the way, B2 formula works and now with your code above, it works PERFECTLY!! THANKS AGAIN!
+ Reply to Thread

 

[Tetra201]

CassieL,

Glad it worked for you.

The problem with the formula is that it does not give you the very last item in the list.

 

[CassieL]

Hi Tetra,

I have another question/request and I hope you can help.
If I want to have C3 locked, how can I add the code in the code you provided above?

I do not want other user to edit the drop down list in C3. Thanks again

 

[Tetra201]

CassieL,

The updated code is shown below. I set the sheet protection password to be your forum nickname. You can change it to whatever you like, including no password ="").

If you want to allow changes to some cells on the copies, those cells have to be unlocked on the original "4075 Wilson" sheet.

Sub Copy_Tabs_New()
    Dim x, ListItem As Variant
    Dim numtimes As Byte
    x = InputBox("Enter number of times to copy 4075 Wilson")
    If Not IsNumeric(x) Then Exit Sub
    If (x < 1) Or (x > 55) Then Exit Sub
    ActiveWorkbook.Sheets("4075 Wilson").Select
    ListItem = Range("C3").Value
    Application.ScreenUpdating = False
    For numtimes = 1 To x
        ActiveWorkbook.Sheets("4075 Wilson").Copy After:=ActiveSheet
        Range("H3").Value = ListItem
        Range("C3").Value = Range("B2").Value
        ListItem = Range("C3").Value
[COLOR=#FF0000]        Range("C3").Locked = True
        ActiveSheet.Protect Password:="CassieL"
[/COLOR]    Next
    ActiveWorkbook.Sheets("4075 Wilson").Select
    Application.ScreenUpdating = True
End Sub