PDA

View Full Version : List of Worksheet Tab Names



r_mundhra
08-24-2007, 07:27 AM
Hi All

Is there any way of creating a list of all the sheet names (i.e Tab names) in a particular workbook. Say I am working in on a Workbook having Sheets 1 to 10. I wouls like to list (by Excel function or thru macro) all the worksheet names at a single place in say worksheet 1. Pl. help./Rajesh

Bob Phillips
08-24-2007, 07:35 AM
For i = 1 To Worksheets.Count
Worksheet("Sheet1").Cells(i,"A").Value = Worksheets(i).Name
Next i

lior03
08-24-2007, 08:44 AM
dear xld
thats what i made out of your code.can you symplfy it.can you show how to add in coplumn B a list of cell B2 of each sheet?

Sub addlistofsheets()
Dim ash As Worksheet
If SheetExists("list") = False Then GoTo err
For i = 1 To Worksheets.Count
Worksheets("list").Cells(i, "A").Value = Worksheets(i).name
Range("A:A").Columns.autofit
Next i
Exit Sub
err:
If MsgBox("list of sheets is unavailable,create one?", vbOKOnly + vbCritical, "TOC") = vbOK Then
Set ash = Sheets.Add
ash.name = "list"
ash.Move before:=Sheets(1)
For i = 1 To Worksheets.Count
Worksheets("list").Cells(i, "A").Value = Worksheets(i).name
Range("A:A").Columns.autofit
Next i
End If
End Sub


thanks

mdmackillop
08-24-2007, 09:08 AM
Have you tried this (http://http://www.vbaexpress.com/kb/getarticle.php?kb_id=16)?

r_mundhra
08-24-2007, 09:11 AM
Thanx very much XLD for tje solution...its working

mdmackillop
08-24-2007, 09:22 AM
Hi Moshe,
Something like

Sub ListB2()
Dim sh As Worksheet, i as Long
For Each sh In Worksheets
If sh.Index <> ActiveSheet.Index Then
i = i + 1
Cells(i, 2) = sh.Cells(2, 2)
End If
Next
End Sub

lior03
08-24-2007, 01:03 PM
could you merge them into one?

Sub addlistofsheets()
Dim ash As Worksheet
If SheetExists("list") = False Then GoTo err
For i = 1 To Worksheets.Count
Worksheets("list").Cells(i, "A").Value = Worksheets(i).name
Range("A:B").Columns.autofit
Next i
Exit Sub
err:
If MsgBox("list of sheets is unavailable,create one?", vbOKOnly + vbCritical, "TOC") = vbOK Then
Set ash = Sheets.Add
ash.name = "list"
ash.Move before:=Sheets(1)
For i = 1 To Worksheets.Count
Worksheets("list").Cells(i, "A").Value = Worksheets(i).name
Range("A:B").Columns.autofit
Next i
End If
End Sub
Sub ListB2()
Dim sh As Worksheet, i As Long
Sheets("list").Select
For Each sh In Worksheets
If sh.index <> ActiveSheet.index Then
i = i + 1
Cells(i + 1, 2) = sh.Cells(2, 2)
End If
Next
End Sub

mdmackillop
08-24-2007, 01:38 PM
Sub addlistofsheets()
Dim ash As Worksheet
On Error Resume Next
Set ash = Sheets("list")
If err = 9 Then
Set ash = Worksheets.Add
ash.Name = "List"
err.Clear
End If
i = 3
For Each sh In Worksheets
If sh.Index <> ash.Index Then
i = i + 1
Cells(i + 1, 1) = sh.Name
Cells(i + 1, 2) = sh.Cells(2, 2)
End If
Next
ash.Range("A:B").Columns.AutoFit
End Sub

lior03
08-27-2007, 06:39 AM
can any one show how to add a sum formula at the bottom of column B?
thanks

lucas
08-27-2007, 06:42 AM
I suggest you start a new thread for this question lior03

lior03
12-30-2007, 02:40 AM
hello
let me take this a little further.suposs i have a workbook with say 6 sheets
january to june.each sheet's column A contains pepole name.column B contain numerical value say wgae paid.can i use excel vba to create sheets for each man and 6 rows with six wages and s sum formula bellow?
thanks

Aussiebear
12-30-2007, 04:03 AM
Hi Moshe,

Please start a new thread. This has absolutely nothing to do with "List of all worksheet tab names".