make a list of sheets to a sheet that named with hyperlink in this lis [SOLVED]

[hoomantt]

hi every professors;

i have an excel file.for example the sheets name are:

MENU

sale

store

stock

visitors

ali

john

kati

hooman

and.....(some Seller names)

first - i need a macro to make a list name of these sheets names(the list name be in the sheet("menu") in column "D").(a list of sheet names that contains Seller names,expect of the sheets that named(menu-sale-store-stock-visitors)

just a list of other sheet names.(just Seller list.)

Second - i want this list of some sheets have hyperlink to the correct sheet that has the same name in this list .(for example when click on "ali" i go to the sheet "ali")and when i go to that sheet i can back to the sheet ("Menu") by pressing a shape or command button or ...

thanks to every one to help me .

 

[jeffreyweir]

hoomantt: if you google "Excel Create Table of Contents" you'll get lots of methods on how to do this.


Here's one.

http://blog.contextures.com/archives/2008/12/17/create-a-table-of-contents-in-excel/

 

[SirJB7]

Hi, hoomantt!


Regarding your questions, here's the code:

-----

[pre]

Option Explicit

Sub IAmTheGenieInTheBottleYouOrderIObbeyMyOwner()
' constants
Const ksWS = "MENU"
Const kiColumn = 4
Const ksWSExcluded = "MENU Hoja3 Hoja5 Hoja6 "
' declarations
Dim I As Integer, J As Integer, A As String
' start
' process
With Worksheets(ksWS)
Range(.Cells(1, kiColumn), .Cells(.Rows.Count, kiColumn)).ClearContents
J = 0
For I = 1 To ActiveWorkbook.Worksheets.Count
A = ActiveWorkbook.Worksheets(I).Name
If InStr(ksWSExcluded, A) = 0 Then
J = J + 1
.Cells(J, kiColumn).Formula = _
"=HYPERLINK(""#'" & A & "'!A1"",""" & A & """)"
Worksheets(A).Cells(1, 1).Formula = _
"=HYPERLINK(""#'" & ksWS & "'!" & .Cells(J, kiColumn).Address _
& """,""" & ksWS & """)"
End If
Next I
End With
' end
End Sub

[/pre]
-----


I didn't understand which worksheets should be included or excluded as per your 2nd question.


Regards!

 

[hoomantt]

Thanks alot

SirJB7 and jeffreyweir

SirJB7 , you Are A really proffessor .you are a nice teacher.you are the best.....

really really thank you so much.

i love you man.

 

[SirJB7]

Hi, hoomantt!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: BTW, female or male?