Transfer "Tabs name" to a worksheet

T

tunabread

Hi everyone,

I have a little problem as I need to create many worksheet tabs in
workbook everyday. How can I make a summary of all the tab names that
have in this workbook to a worksheet? (so that I can check on m
progress daily)

Is there a formula or a little macro that can do this?

Eg. I have many tab names:
GR21550, GR21551, GR21553, GR21554,
GC35000, GC35001, GC35002, GC35004,
TR58690, TR58691, TR58692, TR58693,
TL18000, TL18002, TL18003, TL18004,
ZA8700, ZA8701, ZA8702, ZA8703, ZA8704etc....

Thanks and appreciate any help available
 
S

Shatin

Following code is taken from the book "Excel Hacks" by David & Raina Hawley.
What you do is you create an index worksheet. You then insert following code
into the private module of this worksheet. Whenever the index sheet is
activated, an index of all the sheets will be created.

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim lCount As Long
lCount = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
lCount = lCount + 1
With wSheet
.Range("A1").Name = "Start" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _
"Index", TextToDisplay:="Back to Index"
End With
Me.Hyperlinks.Add Anchor:=Me.Cells(lCount, 1), Address:="",
SubAddress:= _
"Start" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub
 
T

tunabread

Hi Shatin,

Thank you for giving me a helping hand.... however I don't know how t
do this:
(You then insert following code into the private module of thi
worksheet.)

I'm not good with this module thingy. Could you elaborate on what
should do to get run this perfectly? Also, how do I activate the macr
to make it work? (Whenever the index sheet is activated, an index o
all the sheets will be created.)

Thanks again :
 
S

Shatin

1. After you've inserted the new Index worksheet, click on its tab on the
bottom.
2. Then right-click, a menu should pop up. Choose View Code.
3. A new window will be opened. Copy and paste following code in the window.
4. Close the window by clicking the button at the top right hand corner
(just like any normal window).
5. Now you're done. Choose any othe worksheet, then come back to this sheet.
You'll see that an index of all the sheets have been generated. Every time
you come back to this worksheet, the index will be created anew.

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim lCount As Long
lCount = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
lCount = lCount + 1
Me.Hyperlinks.Add Anchor:=Me.Cells(lCount, 1), _
Address:="", SubAddress:= _
"Start" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub
 
T

tunabread

Hi Shatin,

You are awesome!!! Just the right thing I want

Appreciate your prompt reply :)
 
Top