using worksheet name to find cell in table of contents.
 

Hi,

I have a workbook with about 40 sheets. The first sheet (a
summary/contents page) has a list with hyperlinks to individual sheets
(named 1 through 38). The list can be sorted a number of ways so that
a return hyperlink from the individual sheet does not always go to the
relevant cell on the first sheet (because of any sorting done after
the link was created). Now my sheets are named 1 to 38 and these
correspond to the list in the first sheet(ie: 38 lines of data). What
I was hoping for was a macro that I could use from the individual
sheets that would recognise the sheet name, say, 25 and then go to the
cell containing 25 in the list on the first sheet, which is by the way
called "list"?

Thanks for any help you can provide.

using worksheet name to find cell in table of contents.
 

How about

Sub TestMe()

Dim shtname As String

shtname = ActiveSheet.Name
Application.Goto Reference:="list"

Selection.Find(What:=shtname, After:=ActiveCell _
, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns _
, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

ActiveCell.Select

End Sub

Regards
Rowan

"RogueSwan" wrote:

Hi,

I have a workbook with about 40 sheets. The first sheet (a
summary/contents page) has a list with hyperlinks to individual sheets
(named 1 through 38). The list can be sorted a number of ways so that
a return hyperlink from the individual sheet does not always go to the
relevant cell on the first sheet (because of any sorting done after
the link was created). Now my sheets are named 1 to 38 and these
correspond to the list in the first sheet(ie: 38 lines of data). What
I was hoping for was a macro that I could use from the individual
sheets that would recognise the sheet name, say, 25 and then go to the
cell containing 25 in the list on the first sheet, which is by the way
called "list"?

Thanks for any help you can provide.

using worksheet name to find cell in table of contents.
 

In general, it is bad programming practice to use the Acitvate
(or Select) method in a Find method. The reason is that the code
will fail if the target value is not found. It is better to write
the code in a fashion similar to the following:

Dim FoundCell As Range
Set FoundCell = Selection.Find(....)
If FoundCell Is Nothing Then
' not found, take apporpriate action
Else
' found, take appropriate action
FoundCell.Select
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Rowan" wrote in message
...
How about

Sub TestMe()

Dim shtname As String

shtname = ActiveSheet.Name
Application.Goto Reference:="list"

Selection.Find(What:=shtname, After:=ActiveCell _
, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns _
, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

ActiveCell.Select

End Sub

Regards
Rowan

"RogueSwan" wrote:

Hi,

I have a workbook with about 40 sheets. The first sheet (a
summary/contents page) has a list with hyperlinks to
individual sheets
(named 1 through 38). The list can be sorted a number of ways
so that
a return hyperlink from the individual sheet does not always
go to the
relevant cell on the first sheet (because of any sorting done
after
the link was created). Now my sheets are named 1 to 38 and
these
correspond to the list in the first sheet(ie: 38 lines of
data). What
I was hoping for was a macro that I could use from the
individual
sheets that would recognise the sheet name, say, 25 and then
go to the
cell containing 25 in the list on the first sheet, which is by
the way
called "list"?

Thanks for any help you can provide.

using worksheet name to find cell in table of contents.
 

This should work. add some error trapping.

Sub gotolist()

For Each cell In Sheets("list").Range("a1:a10")

If cell.Value = ActiveSheet.Name Then
MsgBox cell.AddressLocal

x = cell.Address


End If

Next cell

Sheets("list").Range(x).Select

End Sub


Cesar Zapata

using worksheet name to find cell in table of contents.
 

Thanks Chip - point taken!

"Chip Pearson" wrote:

In general, it is bad programming practice to use the Acitvate
(or Select) method in a Find method. The reason is that the code
will fail if the target value is not found. It is better to write
the code in a fashion similar to the following:

Dim FoundCell As Range
Set FoundCell = Selection.Find(....)
If FoundCell Is Nothing Then
' not found, take apporpriate action
Else
' found, take appropriate action
FoundCell.Select
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Rowan" wrote in message
...
How about

Sub TestMe()

Dim shtname As String

shtname = ActiveSheet.Name
Application.Goto Reference:="list"

Selection.Find(What:=shtname, After:=ActiveCell _
, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns _
, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

ActiveCell.Select

End Sub

Regards
Rowan

"RogueSwan" wrote:

Hi,

I have a workbook with about 40 sheets. The first sheet (a
summary/contents page) has a list with hyperlinks to
individual sheets
(named 1 through 38). The list can be sorted a number of ways
so that
a return hyperlink from the individual sheet does not always
go to the
relevant cell on the first sheet (because of any sorting done
after
the link was created). Now my sheets are named 1 to 38 and
these
correspond to the list in the first sheet(ie: 38 lines of
data). What
I was hoping for was a macro that I could use from the
individual
sheets that would recognise the sheet name, say, 25 and then
go to the
cell containing 25 in the list on the first sheet, which is by
the way
called "list"?

Thanks for any help you can provide.