Hi, I want to type something into cell A17 on worksheet 'MASTER' Then I click a button below it and I want excel to automatically open the page the found item is on and highlight the cell. Example: I type 199/456 into cell A17 on the worksheet called MASTER. 199/456 will appear on a worksheet called HANDBOOK SHEETS. I want the cell 199/456 to be selected. Is this possible? Is it too much work to do? I had a look in the findnext help and I couldnt get it to work. Could I not use something like below? Thank you ------------------------------------------------ Private Sub CommandButton1_Click() With Application.FindNext(worksheet.master.range("a17")) .LookIn = "active.workbook" .Range ("a1:b500") .Execute End With End Sub ------------------------------------------------

Try something like this...you probably don't need all of the .Find arguments, but they won't hurt. Sub ActivateSearchCell() For Each sht In ActiveWorkbook.Sheets If sht.Name = "Master" Then GoTo nxtSht With Sheets(sht.Name) Set c = Sheets(sht.Name).Cells.Find(What:=Worksheets("Master").Range("A17"), _ After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then Sheets(sht.Name).Activate Range(c.Address).Select Exit Sub End If End With nxtSht: Next End Sub

Hi DerbyDad03, Thank you for your reply. I have put it into my spreadsheet. The line ***Range(c.Address).Select*** always comes up with an error. I dont understand why. Can you make sense of this? The error is: Run-time error '1004': Select method of Range class failed Thanks

Where did you put the code - in a regular module or in a sheet module? I pretty sure it works as written when placed in a regular module, but might fail if in a sheet module, depending on which sheet the value is found in. In either case try changing the line to be: ActiveSheet.Range(c.Address).Select Let me know if that helps.

Thank you DerbyDad. I used your new line and it works! I dont know how to put it in a module, so it is in my sheet. That seems to work for me. If I put it in a module (is that like a macro?) then when I click my button do I just call the module? Cheers, and thanks for your time writing that script for me. Lee

VBA has various modules that can contain code, and as we've seen, macros can act differently depending on where they are placed. Typically a macro of this type, which is accessing all sheets in the workbook, would be placed in what is typically called a "regular" or "standard" module. Some macros must be placed in a Sheet module and some must be placed in the ThisWorkbook module in order to function correctly. To add a regular module to your workbook, open the VBA editor, click on Insert...Module and a Module1 window should open. Another option is to create a personal.xls file and put your macros in modules in that workbook. If you store a file called personal.xls in the XLStart directory (e.g. C:\Program Files\Microsoft Office\Office\XLStart) Excel will open that file every time you start the application and all macros stored in that file will be available for use in any workbook. If within personal.xls you choose Window...Hide and then save it in XLStart, it will open "hidden" and you won't see it, but the macros will be available.

Wow you have opened up a new window for learning about Excel + VB. Thanks for the info and I will test out the different ways of storing macros. I tested it by creating a new module and that works. Thanks again!!!