I have a "master" spreadsheet where I want to display a list of names
from different workbooks and sheets. I would also like this to be
automated--every time I created a new file with names in a certain
folder, for example, I would want that master sheet to be updated.

I'm not terribly familiar with Excel, but I'm currently working with a
non-profit where are human resources are low.

An addendum--for example, I have a spreadsheet that lists the names of
people in cell A1 of sheet1, sheet2, and sheet3. In the future, I
will be adding sheet4, sheet5, sheet6, etc. Is there a way for that
spreadsheet to detect a new sheet and continue to list those names?

On Dec 26, 12:35 pm, wrote:
I have a "master" spreadsheet where I want to display a list of names
from different workbooks and sheets. I would also like this to be
automated--every time I created a new file with names in a certain
folder, for example, I would want that master sheet to be updated.

I'm not terribly familiar with Excel, but I'm currently working with a
non-profit where are human resources are low.

Try this:
Sub CreateTableOfContents()
' Copyright 2002 MrExcel.com
' Determine if there is already a Table of Contents
' Assume it is there, and if it is not, it will raise an error
' if the Err system variable is 0, you know the sheet is not there
Dim WST As Worksheet
On Error Resume Next
Set WST = Worksheets("Table of Contents")
If Not Err = 0 Then
' The Table of contents doesn't exist. Add it
Set WST = Worksheets.Add(Befo=Worksheets(1))
WST.Name = "TOC"
End If
On Error GoTo 0

' Set up the table of contents page
WST.[A2] = "Table of Contents"
With WST.[A6]
.CurrentRegion.Clear
.Value = "Subject"
End With
WST.[B6] = "Page(s)"
WST.Range("A1:B1").ColumnWidth = Array(36, 12)
TOCRow = 7
PageCount = 0
' Do a print preview on all sheets so Excel calcs page breaks
' The user must manually close the PrintPreview window
Msg = "Excel needs to do a print preview to calculate the number of
pages. "
Msg = Msg & "Please dismiss the print preview by clicking close."
MsgBox Msg
ActiveWindow.SelectedSheets.PrintPreview
' Loop through each sheet, collecting TOC information
' Loop through each sheet, collecting TOC information
For Each s In Worksheets
If s.Visible = -1 Then
s.Select
' Use any one of the following 3 lines
ThisName = ActiveSheet.Name
'ThisName = Range("A1").Value
'ThisName = ActiveSheet.PageSetup.LeftHeader
HPages = ActiveSheet.HPageBreaks.Count + 1
VPages = ActiveSheet.VPageBreaks.Count + 1
ThisPages = HPages * VPages
' Enter info about this sheet on TOC
Sheets("TOC").Select
Range("A" & TOCRow).Value = ThisName
Range("B" & TOCRow).NumberFormat = "@"
If ThisPages = 1 Then
Range("B" & TOCRow).Value = PageCount + 1 & " "
Else
Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount +
ThisPages
End If
PageCount = PageCount + ThisPages
TOCRow = TOCRow + 1
End If
Next s
End Sub


Regards,
Ryan---


--
RyGuy


" wrote:

An addendum--for example, I have a spreadsheet that lists the names of
people in cell A1 of sheet1, sheet2, and sheet3. In the future, I
will be adding sheet4, sheet5, sheet6, etc. Is there a way for that
spreadsheet to detect a new sheet and continue to list those names?

On Dec 26, 12:35 pm, wrote:
I have a "master" spreadsheet where I want to display a list of names
from different workbooks and sheets. I would also like this to be
automated--every time I created a new file with names in a certain
folder, for example, I would want that master sheet to be updated.

I'm not terribly familiar with Excel, but I'm currently working with a
non-profit where are human resources are low.

As an alternative, you can try this:

Sub listnames()
n = Worksheets.Count
For i = 1 To n
sh = Worksheets(i).Name
If sh = "Sheet A" Then
Else
ActiveCell.Value = sh
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub

(obtained from here):
http://www.microsoft.com/office/comm...sloc=en-us&p=1

Regards,
Ryan---


--
RyGuy


"ryguy7272" wrote:

Try this:
Sub CreateTableOfContents()
' Copyright 2002 MrExcel.com
' Determine if there is already a Table of Contents
' Assume it is there, and if it is not, it will raise an error
' if the Err system variable is 0, you know the sheet is not there
Dim WST As Worksheet
On Error Resume Next
Set WST = Worksheets("Table of Contents")
If Not Err = 0 Then
' The Table of contents doesn't exist. Add it
Set WST = Worksheets.Add(Befo=Worksheets(1))
WST.Name = "TOC"
End If
On Error GoTo 0

' Set up the table of contents page
WST.[A2] = "Table of Contents"
With WST.[A6]
.CurrentRegion.Clear
.Value = "Subject"
End With
WST.[B6] = "Page(s)"
WST.Range("A1:B1").ColumnWidth = Array(36, 12)
TOCRow = 7
PageCount = 0
' Do a print preview on all sheets so Excel calcs page breaks
' The user must manually close the PrintPreview window
Msg = "Excel needs to do a print preview to calculate the number of
pages. "
Msg = Msg & "Please dismiss the print preview by clicking close."
MsgBox Msg
ActiveWindow.SelectedSheets.PrintPreview
' Loop through each sheet, collecting TOC information
' Loop through each sheet, collecting TOC information
For Each s In Worksheets
If s.Visible = -1 Then
s.Select
' Use any one of the following 3 lines
ThisName = ActiveSheet.Name
'ThisName = Range("A1").Value
'ThisName = ActiveSheet.PageSetup.LeftHeader
HPages = ActiveSheet.HPageBreaks.Count + 1
VPages = ActiveSheet.VPageBreaks.Count + 1
ThisPages = HPages * VPages
' Enter info about this sheet on TOC
Sheets("TOC").Select
Range("A" & TOCRow).Value = ThisName
Range("B" & TOCRow).NumberFormat = "@"
If ThisPages = 1 Then
Range("B" & TOCRow).Value = PageCount + 1 & " "
Else
Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount +
ThisPages
End If
PageCount = PageCount + ThisPages
TOCRow = TOCRow + 1
End If
Next s
End Sub


Regards,
Ryan---


--
RyGuy


" wrote:

An addendum--for example, I have a spreadsheet that lists the names of
people in cell A1 of sheet1, sheet2, and sheet3. In the future, I
will be adding sheet4, sheet5, sheet6, etc. Is there a way for that
spreadsheet to detect a new sheet and continue to list those names?

On Dec 26, 12:35 pm, wrote:
I have a "master" spreadsheet where I want to display a list of names
from different workbooks and sheets. I would also like this to be
automated--every time I created a new file with names in a certain
folder, for example, I would want that master sheet to be updated.

I'm not terribly familiar with Excel, but I'm currently working with a
non-profit where are human resources are low.