Excel Find Function 3

[sbudzynski]

Is there anyway you can use FIND to search through all sheets in an excel document?

I know there has got to be a way but I haven't had time to mess with it, please let me know!

Thanks,

----------

Steve Budzynski


"So, pass another round around for the kids. Who have nothing left to lose and for those souls old and sold out by the soles of my shoes"

 

[xlhelp]

Of course. Select the sheets you want to search through and it will highlight the sheet and the text you are looking for.

Member AAA - Abolish Abused Abbreviations

 

[sbudzynski]

Perfect, much faster then figuring out myself! I knew it was an easy fix too just didn't know how!

Thanks a lot xlhelp!

----------

Steve Budzynski


"So, pass another round around for the kids. Who have nothing left to lose and for those souls old and sold out by the soles of my shoes"

 

[sbudzynski]

Im sorry, a user is now asking if there is anyway they can search for specific sheet names? The excel file they are working with has a lot of sheets and they have to manually find the sheets they are looking for.

This is not that big of a deal since they are alphabetical but I am curious is there a way to search sheet names?

Thanks for you replies!

----------

Steve Budzynski


"So, pass another round around for the kids. Who have nothing left to lose and for those souls old and sold out by the soles of my shoes"

 

[sahmiele]

This should work.


c = 0
sheetname = Application.InputBox("Enter the Worksheet Name you want to find. " _
& "If you chose this by accident, click cancel.", "Find Worksheet", 0, , , , , 2)
If sheetname = 0 Then End
For Each a In Worksheets
If a.Name = sheetname Then c = c + 1
Next a
If c = 0 Then
MsgBox ("The sheet name you entered does not exist. Please try again.")
End
End If
Sheets(sheetname).Select

 

[sbudzynski]

Where do I put that? I don't know code, I'm just an intern :-(

If you have time could you please clarify.. thanks!

----------

Steve Budzynski


"So, pass another round around for the kids. Who have nothing left to lose and for those souls old and sold out by the soles of my shoes"

 

[xlhelp]

sahmiele,

why go through that much trouble? why not

On Error Resume Next
sheetname = Application.InputBox("Enter the Worksheet Name you want to find. " _
& "If you chose this by accident, click cancel.", "Find Worksheet", 0, , , , , 2)

Sheets(sheetname).Select

Member AAA - Abolish Abused Abbreviations

 

[xlhelp]

Steve,

You would go Alt+F11, Insert, Module and then copy this:

Sub fndsht()
On Error Resume Next
sheetname = Application.InputBox("Enter the Worksheet Name you want to find. " _
& "If you chose this by accident, click cancel.", "Find Worksheet", 0, , , , , 2)

Sheets(sheetname).Select

End Sub


Then you would use the macro through TOlls, Macro, Macros.

Member AAA - Abolish Abused Abbreviations

 

[sahmiele]

Click tools, macros, record macro, click ok. Click tools, macros, stop recording. Click tools, macros, Macros. Click Step into. Paste it in there. Save the file. Whenever you need to run it, click tools, macros, macros, and double click the macro.

As for xlhelp, I went through the trouble so the users know that they typed in a worksheet that doesn't exist, instead of just ending the program. a bit more user friendly for people who aren't excel savvy.

 

[sbudzynski]

THANK YOU BOTH, i will do it now.

----------

Steve Budzynski


"So, pass another round around for the kids. Who have nothing left to lose and for those souls old and sold out by the soles of my shoes"

 

[firefytr]

Or you could sort your worksheets in alphabetical order ...

http://www.cpearson.com/excel/sortws.htm

HTH

-----------
Regards,
Zack Barresse

 

[sbudzynski]

Yeah I got it to work thank you very much for all your help guys

----------

Steve Budzynski


"So, pass another round around for the kids. Who have nothing left to lose and for those souls old and sold out by the soles of my shoes"