list tab name
 

Is there a way of listing all tab names in a workbook into one sheet.

By way of example, I have a questionaire which has been completed by 50
people. Each sheet tab is named as the persons name. I would like to list the
names of each tab onto a summary page and also have the total score for each
tab (which is the same cell on each sheet).

Any help would be appreciated.
A

list tab name
 

This code by Ron de Bruin may fit your needs:
http://www.rondebruin.nl/summary2.htm

Hope this help,
Miguel

"A" wrote:

Is there a way of listing all tab names in a workbook into one sheet.

By way of example, I have a questionaire which has been completed by 50
people. Each sheet tab is named as the persons name. I would like to list the
names of each tab onto a summary page and also have the total score for each
tab (which is the same cell on each sheet).

Any help would be appreciated.
A

list tab name
 

As long as there isn't a worksheet name "Names" this macro will do the trick.

Sub ListNames()

Dim wb As Workbook
Dim ws As Worksheet
Dim strName As String
Dim intOffset As Integer

Set wb = ActiveWorkbook

wb.Sheets.Add
wb.ActiveSheet.Name = "Names"

Range("A1").Select

For Each ws In wb.Worksheets
strName = ws.Name
If strName < "Names" Then
ActiveCell.Offset(intOffset).Value = strName
intOffset = intOffset + 1
End If
Next ws

Set ws = Nothing
Set wb = Nothing

--
Kevin Backmann


"A" wrote:

Is there a way of listing all tab names in a workbook into one sheet.

By way of example, I have a questionaire which has been completed by 50
people. Each sheet tab is named as the persons name. I would like to list the
names of each tab onto a summary page and also have the total score for each
tab (which is the same cell on each sheet).

Any help would be appreciated.
A

list tab name
 

Thanks all

"Kevin B" wrote:

As long as there isn't a worksheet name "Names" this macro will do the trick.

Sub ListNames()

Dim wb As Workbook
Dim ws As Worksheet
Dim strName As String
Dim intOffset As Integer

Set wb = ActiveWorkbook

wb.Sheets.Add
wb.ActiveSheet.Name = "Names"

Range("A1").Select

For Each ws In wb.Worksheets
strName = ws.Name
If strName < "Names" Then
ActiveCell.Offset(intOffset).Value = strName
intOffset = intOffset + 1
End If
Next ws

Set ws = Nothing
Set wb = Nothing

--
Kevin Backmann


"A" wrote:

Is there a way of listing all tab names in a workbook into one sheet.

By way of example, I have a questionaire which has been completed by 50
people. Each sheet tab is named as the persons name. I would like to list the
names of each tab onto a summary page and also have the total score for each
tab (which is the same cell on each sheet).

Any help would be appreciated.
A