Changing to a sheet with same name as cell text

[Yeoman.jeremy]

Hi there.

I want to be able to change to a worksheet within my workbook depending on the value a specific cell shows.

The cell in question has a validation list - each of the options to choose from are also names of worksheets in the workbook.

When i press a command button, I want it to switch to the sheet which the cell says.

A quick rundown on why:

I have made a sheet which adds products to different suppliers.
The user will enter in all the details to the necessary fields, and when the user presses the command button, it will copy and paste the values from the various fields into the appropriate rows and columns on the relevant page.
There are (i think) about 6 different pages they could be going to.


I think i can manage the copy and paste code, I just am not sure how to switch to a sheet that the cell says.
In english, it looks like this.
*presses button*
put ALL this information in THIS *points to cell (J3)* page

please help! This is the last thing i need for my excell workbook before i make a presentation in 2 weeks!!!!

 

[FishingIsMoreFun]

This might get you started, it has to go in the cmd button code.
It will select the sheetname that is held in text within a selected cell.
It will of course jump to that sheet, so you would have to return to that sheet manually to select again.

Is also case and space sensitive.

Private Sub CommandButton1_Click()

Dim wkSheet As Worksheet
For Each wkSheet In Application.Worksheets

If ActiveCell.Value = wkSheet.Name Then
Sheets(wkSheet.Name).Select
End If

Next wkSheet

End Sub

You can then use

ActiveSheet.Range("J3").Select

 

[Yeoman.jeremy]

Great, this works perfectly - The only problem being is that the sheets i want to open are hidden.
Is there a way I can code to unhide/hide a worksheet?

I'm planning to switch in back'n'forth between the two sheets to copy and paste etc, so ill be using

Application.ScreenUpdating=false

anyway (so it wont look messy)

is there anything like

sheets("example sheet").hide

that i can use?

 

[FishingIsMoreFun]

Private Sub CommandButton1_Click()
Dim wkSheet As Worksheet
For Each wkSheet In Application.Worksheets

If ActiveCell.Value = wkSheet.Name Then
Sheets(wkSheet.Name).Visible = True
Sheets(wkSheet.Name).Select
End If

Next wkSheet
ActiveSheet.Range("J3").Select

End Sub

To hide sheet.

Sheets(wkSheet.Name).Visible = False

 

[Yeoman.jeremy]

This worked brilliant until a few weeks ago when it stopped working.
When I step through the code, it hits the

If ActiveCell.Value = wkSheet.Name Then

and skips all the "Then" stuff.

When I hover my mouse over wkSheet.Name piece of this code, it shows the name of only my first sheet in the workbook.

 

[FishingIsMoreFun]

What error message does it return?

 

[Yeoman.jeremy]

It doesn't return any errors, it just hit the IF and skips to the end of the if

 

[FishingIsMoreFun]

Are you sure there is a hidden worksheet of the same name as the selected celll?

 

[Yeoman.jeremy]

yup. What i thought was interesting was that when i hovered the mouse over
wkSheet.Name
It had a popup window saying the name of my first sheet in the list

 

[FishingIsMoreFun]

You will have to post the code up (in code brackets), it is useless for me to try and guess as the code I supplied appears to be only an example, not an end solution script.