create list from data on multiple sheets

Hi,

I am not an IT and I really really need help.

I have data contained in matching cells from several sheets. I need a worksheet that reflect the summary of the data from the worksheets. No formula is required. I just want my summary worksheet to list the all the data from my other worksheets. I might end up having about a hundred worksheets that I need included in the summary. Attached is a sample.

It would be better if the summary automaticaaly updates as new worksheets are added.

Thank you.

Put this macro in the SheetModule for SUMMARY:

Option Explicit

Private Sub Worksheet_activate()
Application.EnableEvents = False
    Call AssembleSummary
Application.EnableEvents = True
End Sub

Put this macro into a normal module:

Option Explicit

Sub AssembleSummary()
Dim ws As Worksheet, NR As Long
Application.ScreenUpdating = False
Sheets("Summary").Activate
Range("A4:AM" & Rows.Count).ClearContents
NR = 4

    For Each ws In Worksheets
        If LCase(ws.Name) <> "summary" Then
            ws.Range("B3:B7,B9:B12,B14:B15,B17:B20,B22,B24:B27,B31:B33").Copy
                Range("A" & NR).PasteSpecial Paste:=xlPasteValues, Transpose:=True
            ws.Range("B34,B36:B37,B41:B43,B46:B50,B53:B55").Copy
                Range("Z" & NR).PasteSpecial Paste:=xlPasteValues, Transpose:=True
            NR = NR + 1
        End If
    Next ws

Range("A1").Select
Application.ScreenUpdating = True
End Sub

If you want to add a Forms button to the sheet, you can, then you can attach the macro to it. But the _activate macro given above will cause the SUMMARY sheet to reassert with all the data from the other sheets every time you select the sheet.

In Excel 2007, this will have to be a .xlsm file. (sample file attached)

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)