This macro loops through all the worksheets in a workbook, placing th
value of cell "a1" into an array, then inserts a new page at the star
of the workbook, and inserts the value in successive cells from cel
a2.

Sub loop_and_list()
Dim myarray()
n = 0

For Each ws In ActiveWorkbook.Worksheets
ReDim Preserve myarray(n)
myarray(n) = ws.Range("a1").Value
n = 1 + n
Next

Sheets.Add befo=Sheets(1)
Sheets(1).Select
Range("a2").Select

For n = LBound(myarray) To UBound(myarray)
ActiveCell.Value = myarray(n)
ActiveCell.Offset(1, 0).Select
Next

End Su

--
Message posted from http://www.ExcelForum.com