Move to a worksheet continaing a value in a specific cell on a header page

Hi

I have a workbook containing 100 worksheets. Cell E4 in each sheet (except the header sheet) contains its own reference, and the header sheet contains a list of those references. The worksheet names don't contain the reference, and can change.

What I want to do is use data validation in a cell on the header sheet to choose one of those references, and then move to the worksheet that contains that reference in cell E4. I've found some search tools, but can't work out how to exclude the header page from the search - as any search that I do returns the value in the list on the header page.

I can work in VB but only at a very basic level, any help you can give would be most appreciated!

Thanks, Robin

Robin,
If you have a code, please post it

This is the code that I'm trying to use - except that I can't get it to work!


Private Sub CommandButton3_Click()



Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=Range("e4"), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub

Robin,
Please wrap your code with tags. It is forum's rule to use code tags with your code. You can edit the above code by adding tags.
Click edit then select the whole code, then select the sign # from the quick reply

This code does not copy
These lines, assuming datatoFind is E4, either exit the sub ones it finds the value, if not, it displays on message box. I think it would be easy if you could down load a sample?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks, is that how it's supposed to be?

I would download a code, but can't find one that does what I need unfortunately

Not a code, an excel sample where you are working, or have tested the code

Doh! Here is a sample - a user just chooses form the list in cell F3 on sheet one, clicks the button and it'll take them to the sheet that has the selected value in cell E4. Or not, so far anyway!

Robin,
The code could be improved, but first need to understand. As it is not the code is stuck in sheet1, it is not looping through the sheets.
I can see you have a drop down lists in F3 sheet1 and can also see these lists ie, G1,G2 appear in E4 in each sheet.
Now, what are you searching for and when the search is found ,what is to be copied, moved and where?

Thanks, the code is botched I know!

All I'm after is making the worksheet that contains the reference the active sheet. Nothing needs to be copied or moved at all

Marvellous!

The last bit is to make the sheet that's been found the active sheet.

The hassle is becauase the user has to flit back and forth between individual sheets and the header over and over.

Thanks for the help, we're nearly there!

I have added two lines to the above code(Please see above code)

Please Login or Register  to view this content.

The second line may not be important.
I really do not know how to make it active. This code when it finishes takes you to the active sheet.

Thank you very much - that does the trick nicely!

I really appreciate the help - cheers!

Robin,
Well! I am a bit surprised if that you wanted.
Glad to be helpful anyway!
Could you please now close this thread as solved? Go in to the top right-hand of this page, choose "Thread Tools" then select Solved from the drop down menu.