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

 

[CassieL]

Hi Tetra201, Thank you very much! It works perfectly!

 

[CassieL]

Hello Tetra, I ran into a small issue, it might be obvious to me but I can't figure that out.

As above code I have C3 locked, however, the two macro button in the sheet does not work when the cell is locked. How can I go around with that? I am sorry if I giving trouble, but I am appreciate your help very much! Thanks!

 

[Tetra201]

Hi CassieL,

Once again , some more info needed:
What are those buttons -- Form Controls? ActiveX Controls?
What macros do they fire?
Are they linked to cell C3?
What does "not work" mean -- disabled/greyed out?

 

[CassieL]

Hi Tetra, thanks again for your reply!

There is 2 button in the sheet, and they are Form Controls :

Sub Hide_Hist()Columns("L:W").EntireColumn.Hidden = True
End Sub
Sub ShowH()
Columns("L:W").EntireColumn.Hidden = False
End Sub

There are not linked to cell C3, they are just hide or unhide column L:W.

When I start copy the tab, my C3 is locked, but when I click on those button, I have the following error:

Run-time error'1004':
Unable to set the Hidden property of the Range class

Is there anyway to go around with that? Thank you once again! = )

 

[Tetra201]

Here is an updated code that resolves the issue (additions are in red):

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
        Range("C3").Locked = True
        ActiveSheet.Protect Password:="CassieL"[COLOR=#FF0000], AllowFormattingColumns:=True[/COLOR]
    Next
    ActiveWorkbook.Sheets("4075 Wilson").Select
    Application.ScreenUpdating = True
End Sub

 

[CassieL]

THIS IS GREAT!!! THANK YOU VERY MUCH Tetra!

 

[Tetra201]

Always glad to help.