List of Worksheet Tab Names

[r_mundhra]

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]

[vba]

For i = 1 To Worksheets.Count
Worksheet("Sheet1").Cells(i,"A").Value = Worksheets(i).Name
Next i
[/vba]

[lior03]

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?
[VBA]
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

[/VBA]
thanks

[mdmackillop]

Have you tried this?

[r_mundhra]

Thanx very much XLD for tje solution...its working

[mdmackillop]

Hi Moshe,
Something like
[VBA]
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

[/VBA]

[lior03]

could you merge them into one?
[VBA]
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
[/VBA]

[mdmackillop]

[VBA]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
[/VBA]

[lior03]

can any one show how to add a sum formula at the bottom of column B?
thanks

[lucas]

I suggest you start a new thread for this question lior03

[lior03]

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]

Hi Moshe,

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