Populate cell values with sheet names
 

This is a simple example. Customize to suit. This example uses the
Workbook's NewSheet event to update the summary sheet when a new sheet is
added to the workbook. You will need to "refresh" the summary sheet, by
calling Summarize separately to update data. You may want to manually
refresh the summary sheet instead of having the NewSheet event trigger an
update, or you may want to add refresh triggers on other workbook or
worksheet events. Up to you.

Summarize rewrites all the data. You may want to use Summarize to refresh
the entire sheet, but supply other procedures to merely add new sheet names
to the list when sheets are added or to add or update data when it changes.
There are many possibilities. Look at Worksheet and Workbook events for
possible update triggers.

'In a standard module
Sub Summarize()
Dim rowIndex As Long
rowIndex = 0
Dim wks As Worksheet
Dim wksSummary As Worksheet
Set wksSummary = ThisWorkbook.Worksheets("Summary") 'assumes a worksheet
named "Summary"
For Each wks In ThisWorkbook.Worksheets
If Not wks Is wksSummary Then
rowIndex = rowIndex + 1
'copy over some data
'could also insert formulas into summary sheet referencing other
worksheet cells
wksSummary.Columns(1).Rows(rowIndex).Value = wks.Name
wksSummary.Columns(2).Rows(rowIndex).Value = wks.Range("A1").Value
wksSummary.Columns(2).Rows(rowIndex).Resize(1, 2).Value =
wks.Range("B1:C1").Value
End If
Next wks
End Sub

'In ThisWorkbook module
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.ScreenUpdating = False
Sh.Move After:=Sheets(Sheets.Count)
Call Summarize
ThisWorkbook.Worksheets("Summary").Activate
Application.ScreenUpdating = True
End Sub


"Tracy W." <Tracy wrote in message
...
I am trying to build a worksheet that will take the names of the
subsequent
worksheets in the file, and populate new rows for each of the names and
possibly pull data from certain cells on those sheets to form a "summary"
page if you will. Problem is, I have no idea how to pull the names of the
worksheets, and automatically create new rows on this summary page.
Obviously, this can be done by linking the cells, but this is a manual
process, and can be tedious. I'd like to have it where if I add or delete
a
worksheet, the summary page is automatically updated, and I don't have to
keep making changes. Any ideas are greatly appreciated.