find a sheet by a cell

[John]

In a workbook I want to find the workssheet that has a a particular name
in cell C1, make it the active sheet and get the name(tab) of the sheet
into a variable.

Help
JOhn

 

[John]

The sub you offered is the way I've been doing it. I was hoping for a
faster search such as using "Find." There's over 500 sheets and I'm
essentially finding all of them out of order so to speak. It seems a
shame to start from 1 every time.

By using Record MAcro I came up with

Cells.Find(What:="John G", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I just activate the workbook and sheet1 cells(1,1) and use that and it
gets it fast. But then I'm stuck. If I'm in VB I still don't know what
sheet I'm in.

Hi,

In VBA you can use this function:

The function FindSheetName("String to find in C1") will return the name
of the sheet.


Code:
--------------------
Sub Main()

MsgBox FindSheetName("fr")

End Sub

Function FindSheetName(NameStr As String) As String
Dim x As Integer

FindSheetName = "Not Found!"

For x = 1 To ActiveWorkbook.Sheets.Count
If Sheets(x).Cells(1, 3) = NameStr Then
FindSheetName = Sheets(x).Name
End If
Next x

End Function
--------------------


Charlie
'Opener Consulting Home' (http://www.openerconsulting.com)

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

that's the formula offered in the "Look at this" you offered. It makes
no sense to me. What does "filename" refer to? What is the "]" for?

I want to find where the string "John G" is in a workbook and then
save
the name of the worksheet and the location of the cell. What has that
got to do with the formula above?

I found this formula before and it just baffled me. I've never seen an
example of it's use... only the formula.
Thanks
John

Maybe this:

Private Sub Workbook_Open()
Sheets("Sheet1").Activate
End Sub

It needs to go in 'ThisWorkbook', object.

Also, take a look at this:
'Cell Function Returns Sheet Name, Workbook Name and Path' (http://tinyurl.com/72rxjh)
Remember, you have to save your WB before the change becomes effective.
Regards,
Ryan---

 

[John]

Yeh... activesheet.name... that does it. Thanks
John

Hi,

here:


Code:
--------------------
Sub Macro4()
Sheets.Select
Range("C1:C1").Select
Selection.Find(What:="821 -", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

MsgBox ActiveSheet.Name


End Sub
--------------------

The sub you offered is the way I've been doing it. I was hoping for a
faster search such as using "Find." There's over 500 sheets and I'm
essentially finding all of them out of order so to speak. It seems a
shame to start from 1 every time.

By using Record MAcro I came up with

Cells.Find(What:="John G", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I just activate the workbook and sheet1 cells(1,1) and use that and it
gets it fast. But then I'm stuck. If I'm in VB I still don't know what
sheet I'm in.

Hi,

In VBA you can use this function:

The function FindSheetName("String to find in C1") will return the name
of the sheet.


Code:
--------------------
Sub Main()

MsgBox FindSheetName("fr")

End Sub

Function FindSheetName(NameStr As String) As String
Dim x As Integer

FindSheetName = "Not Found!"

For x = 1 To ActiveWorkbook.Sheets.Count
If Sheets(x).Cells(1, 3) = NameStr Then
FindSheetName = Sheets(x).Name
End If
Next x

End Function
--------------------


Charlie
'Opener Consulting Home' ('Opener Consulting Home' (http://www.openerconsulting.com))

John;184098 Wrote:
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

that's the formula offered in the "Look at this" you offered. It makes
no sense to me. What does "filename" refer to? What is the "]" for?

I want to find where the string "John G" is in a workbook and then
save
the name of the worksheet and the location of the cell. What has that
got to do with the formula above?

I found this formula before and it just baffled me. I've never seen an
example of it's use... only the formula.
Thanks
John


ryguy7272 wrote:
Maybe this:

Private Sub Workbook_Open()
Sheets("Sheet1").Activate
End Sub

It needs to go in 'ThisWorkbook', object.

Also, take a look at this:
'Cell Function Returns Sheet Name, Workbook Name and Path'
('Cell Function Returns Sheet Name, Workbook Name and Path' (http://tinyurl.com/72rxjh))
Remember, you have to save your WB before the change becomes
effective.
Regards,
Ryan---