vijyat
10-15-2014, 07:22 PM
Hi,
I have an excel file with 29 sheets. All sheets are numbered in accordance with the code that I wrote. The dropdown menu is linked to Cell "O2" in all the sheets, just as a placeholder. The linked cell can be changed. When the first value in the list is chosen from dropdown the O2 Cell displays a value of 1 and so on and so forth. I am looking for some help to reduce the code as I believe there are better ways to write it and I can learn at the same time. The code works perfectly at the moment.
Appreciate any help.
Thanks
Function GoSheet(strWS)
Sheets(strWS).Select
Range("A1").Activate
End Function
Sub LaunchSheet()
If Range("O2") = 1 Then
GoSheet (Sheet1.Name)
ElseIf Range("O2") = 2 Then
GoSheet (Sheet2.Name)
ElseIf Range("O2") = 3 Then
GoSheet (Sheet3.Name)
ElseIf Range("O2") = 4 Then
GoSheet (Sheet4.Name)
ElseIf Range("O2") = 5 Then
GoSheet (Sheet5.Name)
ElseIf Range("O2") = 6 Then
GoSheet (Sheet6.Name)
ElseIf Range("O2") = 7 Then
GoSheet (Sheet7.Name)
ElseIf Range("O2") = 8 Then
GoSheet (Sheet7.Name)
ElseIf Range("O2") = 9 Then
GoSheet (Sheet9.Name)
ElseIf Range("O2") = 10 Then
GoSheet (Sheet10.Name)
ElseIf Range("O2") = 11 Then
GoSheet (Sheet11.Name)
ElseIf Range("O2") = 12 Then
GoSheet (Sheet12.Name)
ElseIf Range("O2") = 13 Then
GoSheet (Sheet13.Name)
ElseIf Range("O2") = 14 Then
GoSheet (Sheet14.Name)
ElseIf Range("O2") = 15 Then
GoSheet (Sheet15.Name)
ElseIf Range("O2") = 16 Then
GoSheet (Sheet16.Name)
ElseIf Range("O2") = 17 Then
GoSheet (Sheet17.Name)
ElseIf Range("O2") = 18 Then
GoSheet (Sheet18.Name)
ElseIf Range("O2") = 19 Then
GoSheet (Sheet19.Name)
ElseIf Range("O2") = 20 Then
GoSheet (Sheet20.Name)
ElseIf Range("O2") = 21 Then
GoSheet (Sheet21.Name)
ElseIf Range("O2") = 22 Then
GoSheet (Sheet22.Name)
ElseIf Range("O2") = 23 Then
GoSheet (Sheet23.Name)
ElseIf Range("O2") = 24 Then
GoSheet (Sheet24.Name)
ElseIf Range("O2") = 25 Then
GoSheet (Sheet25.Name)
ElseIf Range("O2") = 26 Then
GoSheet (Sheet26.Name)
ElseIf Range("O2") = 27 Then
GoSheet (Sheet27.Name)
ElseIf Range("O2") = 28 Then
GoSheet (Sheet28.Name)
ElseIf Range("O2") = 29 Then
GoSheet (Sheet29.Name)
End If
End Sub
I have an excel file with 29 sheets. All sheets are numbered in accordance with the code that I wrote. The dropdown menu is linked to Cell "O2" in all the sheets, just as a placeholder. The linked cell can be changed. When the first value in the list is chosen from dropdown the O2 Cell displays a value of 1 and so on and so forth. I am looking for some help to reduce the code as I believe there are better ways to write it and I can learn at the same time. The code works perfectly at the moment.
Appreciate any help.
Thanks
Function GoSheet(strWS)
Sheets(strWS).Select
Range("A1").Activate
End Function
Sub LaunchSheet()
If Range("O2") = 1 Then
GoSheet (Sheet1.Name)
ElseIf Range("O2") = 2 Then
GoSheet (Sheet2.Name)
ElseIf Range("O2") = 3 Then
GoSheet (Sheet3.Name)
ElseIf Range("O2") = 4 Then
GoSheet (Sheet4.Name)
ElseIf Range("O2") = 5 Then
GoSheet (Sheet5.Name)
ElseIf Range("O2") = 6 Then
GoSheet (Sheet6.Name)
ElseIf Range("O2") = 7 Then
GoSheet (Sheet7.Name)
ElseIf Range("O2") = 8 Then
GoSheet (Sheet7.Name)
ElseIf Range("O2") = 9 Then
GoSheet (Sheet9.Name)
ElseIf Range("O2") = 10 Then
GoSheet (Sheet10.Name)
ElseIf Range("O2") = 11 Then
GoSheet (Sheet11.Name)
ElseIf Range("O2") = 12 Then
GoSheet (Sheet12.Name)
ElseIf Range("O2") = 13 Then
GoSheet (Sheet13.Name)
ElseIf Range("O2") = 14 Then
GoSheet (Sheet14.Name)
ElseIf Range("O2") = 15 Then
GoSheet (Sheet15.Name)
ElseIf Range("O2") = 16 Then
GoSheet (Sheet16.Name)
ElseIf Range("O2") = 17 Then
GoSheet (Sheet17.Name)
ElseIf Range("O2") = 18 Then
GoSheet (Sheet18.Name)
ElseIf Range("O2") = 19 Then
GoSheet (Sheet19.Name)
ElseIf Range("O2") = 20 Then
GoSheet (Sheet20.Name)
ElseIf Range("O2") = 21 Then
GoSheet (Sheet21.Name)
ElseIf Range("O2") = 22 Then
GoSheet (Sheet22.Name)
ElseIf Range("O2") = 23 Then
GoSheet (Sheet23.Name)
ElseIf Range("O2") = 24 Then
GoSheet (Sheet24.Name)
ElseIf Range("O2") = 25 Then
GoSheet (Sheet25.Name)
ElseIf Range("O2") = 26 Then
GoSheet (Sheet26.Name)
ElseIf Range("O2") = 27 Then
GoSheet (Sheet27.Name)
ElseIf Range("O2") = 28 Then
GoSheet (Sheet28.Name)
ElseIf Range("O2") = 29 Then
GoSheet (Sheet29.Name)
End If
End Sub