Summary sheet

**cope** · 07-22-2009, 02:19 PM

I have about 100 sheets in my workbook, and I want to create summary sheets which will take for example the cells A2, B4 and C6. It would then put this information in the summary sheet, each row representing a sheet.

Is there anyway to do so, without making the link one by one for each sheet?

Thanks a lot,

Cope

**mrice** · 07-22-2009, 02:44 PM

You can do this easily by macro. Please see the attachment for an example.

You can edit the code if needed by hitting ALT F11 and looking on the module1 tab.

Run the macro by hitting the GO button on the Summary tab. Note the values in the top row which correspond to the addresses that you need. You can add to this as necessary.

Sub Test()
RowCount = 1
For Each Sheet In ActiveWorkbook.Sheets
    If Sheet.Name <> "Summary" Then
        RowCount = RowCount + 1
        Sheets("Summary").Cells(RowCount, 1) = Sheet.Name
        For N = 2 To Sheets("Summary").Cells(1, Columns.Count).End(xlToLeft).Column
            Sheets("Summary").Cells(RowCount, N) = Sheet.Range(Sheets("Summary").Cells(1, N))
        Next N
    End If
Next Sheet
End Sub

**cope** · 07-22-2009, 03:31 PM

It worked great, but it's not what I expected. What I would prefer is not only the data but a link to the actual sheets.

Thanks a lot for this one,

Cope

**mrice** · 07-22-2009, 04:09 PM

Try this replacement

Sub Test()
RowCount = 1
For Each Sheet In ActiveWorkbook.Sheets
    If Sheet.Name <> "Summary" Then
        RowCount = RowCount + 1
        Sheets("Summary").Cells(RowCount, 1) = Sheet.Name
        For N = 2 To Sheets("Summary").Cells(1, Columns.Count).End(xlToLeft).Column
            Sheets("Summary").Cells(RowCount, N).Formula = "=" & Sheet.Name & "!" & Cells(1, N)
        Next N
    End If
Next Sheet
End Sub