Help in Excel

[Rahulwork]

Hi Team

I have 10 worksheets in Excel.

Name of sheet A, B, ........J

I have a drop down at sheet 11 -- @ A1 in which all sheet's name are there.

Now i want when i select B from drop down and click on button. macro automatically show me sheet B.

Please help me in this. do let me know in case of any further query or clarification on question

Regards
Rahul

 

[pdevito3]

Make a copy of your workbook. This way you won't mess up the original somehow. Press alt+f11 and find the sheet 11 on the left side. paste the code below into the sheet and try changing your combobox

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Dim ws As Worksheet

If Target.Address = "$A$1" Then
    For Each ws In Worksheets
        Select Case ws.Name
            Case Range("A1").Value
                ws.Visible = True
            Case ActiveSheet.Name
                ws.Visible = True
            Case Else
                ws.Visible = False
        End Select
    Next ws
End If

Application.ScreenUpdating = True

End Sub

 

[Rahulwork]

I have this code

Sub Button1_Click()
Worksheets(Range("A1").Value).Activate
End Sub

by this desire sheet is selected but i want to specify the cell as well. please help

 

[jtakw]

May be this:

Sub Button1_Click()
 Worksheets(Range("A1").Value).Activate
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Cells(1, 4).Select
 End Sub

1 is row index, 4 is column index, insert whatever you need.

 

[FormR]

but i want to specify the cell as well. please help

Another option:

Sub Button1_Click()
Application.Goto Worksheets(Range("A1").Value).Range("C10"), True
End Sub

 

[pdevito3]

Whoops, misread. This should do the trick:

Sub Button1_Click()
Worksheets(Range("A1").Value).Range("[COLOR=#ff0000]A2[/COLOR]").Activate 'change the range as appropriate where it says "A2" 
End Sub

If you want to hide all the sheets too, use this code:

Option Explicit  
Private Sub Button1_Click()  

Application.ScreenUpdating = False  Dim ws As Worksheet  If Target.Address = "$A$1" Then     For Each ws In Worksheets         Select Case ws.Name             Case Range("A1").Value                 ws.Visible = True             Case ActiveSheet.Name                 ws.Visible = True             Case Else                 ws.Visible = False         End Select     Next ws End If [COLOR=#ff0000]
Worksheets(Range("A1").Value).Range("A2").Activate[/COLOR] 'change the range as appropriate where it says "A2"  

Application.ScreenUpdating = True
  
End Sub

</pre>