Generate Summary Report Using VBA

Hi Guys,

I presume that there is some easy way to do what I am trying to do but I have been unable to find the code. I have one workbook with 90+ worksheets in it and it is constantly growing. Over time people will add more worksheets of unknown worksheet names (ie, not something easy like Sheet1, Sheet2 etc). Each worksheet has a standard format.

I have a macro to organise worksheets alphabetically and generate a table of contents of worksheets. So that whenever anybody adds another worksheet, they run these macros and it updates the table of contents and sorts everything alphabetically. Simple.

I want to take this a step further and display additional information next to the worksheet names in the table of contents to generate a summary report.

ie, I need a macro to look up a specific cell in each worksheet and present the cell content next to the appropriate name in the table of contents. This should be easy but because the worksheet names are variable and more worksheets will be added over time I do not know how to handle this.

I can use the "sheets.select" method, and copy paste to the appropriate cell in VBA, but this seems like a rediculous solution and would require constant updating as more worksheets are added. Is there a better way?

I have tried modifying my macro for the table of contents so that instead of displaying the worksheet name in the table of contents it displays the conents of a cell within the worksheet, but I have not had much luck.

I am really stuck. Any suggestions? Thanks in advance

HI
try this macro

Sub summary()
Dim a As Long
For a = 2 To Sheets.Count
Sheets("summary").Cells(a, 1) = Worksheets(a).Name
Sheets("summary").Cells(a, 2) = Worksheets(a).Cells(1, 1)
Next a
End Sub

It will list all sheet names in col A and the value in A1 to col B. of sheet summary. Ravi