Hi everyone,

I have a little problem as I need to create many worksheet tabs in a
workbook everyday. How can I make a summary of all the tab names that I
have in this workbook to a worksheet? (so that I can check on my
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!


--
tunabread
------------------------------------------------------------------------
tunabread's Profile: http://www.excelforum.com/member.php...o&userid=21561
View this thread: http://www.excelforum.com/showthread...hreadid=466578

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


Hi everyone,

I have a little problem as I need to create many worksheet tabs in a
workbook everyday. How can I make a summary of all the tab names that I
have in this workbook to a worksheet? (so that I can check on my
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!


--
tunabread
------------------------------------------------------------------------
tunabread's Profile:
http://www.excelforum.com/member.php...o&userid=21561
View this thread: http://www.excelforum.com/showthread...hreadid=466578

Hi Shatin,

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

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

Thanks again :)


--
tunabread
------------------------------------------------------------------------
tunabread's Profile: http://www.excelforum.com/member.php...o&userid=21561
View this thread: http://www.excelforum.com/showthread...hreadid=466578

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

Hi Shatin,

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

Appreciate your prompt reply :)


--
tunabread
------------------------------------------------------------------------
tunabread's Profile: http://www.excelforum.com/member.php...o&userid=21561
View this thread: http://www.excelforum.com/showthread...hreadid=466578