Creating auto updating Summary Sheet

Attached is the file I downloaded (although modified so it now doesn't seem to work correctly), but gives the gist of what I was tryint to do.

Compiling table from data in other tabs.xls

Think I may have solved most of this by looking at other threads.

1) If I add this macro to another workbook, how do I configure the workbook to be able to run this macro? (dummies guide would be good)

Answer: Routine must be called "Private Sub Worksheet_Activate()" and inserted into the worksheet using the "View Code" option when right clicking the Tab.

2) How can I modify this macro to look at specific columns and that are spread over the worksheet e.g. column A, D, F, X, Y etc? (I could re-arrange the data, but for asthetic reasons would rather not).
3) Is there any easier method for creating a summary sheet of data of an inderminable size and spread across an undeterminable number of tabs?

Answer: I've found the following code useful.

Sub Summary_Consolidate_Specific_Columns()

'ExcelForum - Modified from Oeldere solution in thread 1028798 (Populate data from multiple worksheets into main report).

Dim wsTest As Worksheet

'check if sheet "Consolidated" already exist

Const strSheetName As String = "Consolidated"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

With Sheets("Consolidated")
    .UsedRange.ClearContents
'   Arrange columns as required
    .Range("A1:F1").Value = Array("sheet", "Date", "Module", "Project", "Passed", "Failed")
    For Each Sh In Sheets
        With Sh
'           Check name of sheet to exclude those not required
            If .Name <> "Consolidated" And .Name <> "Main Sheet (After)" And .Name <> "Main Sheet (Before)" And .Name <> "PivotTable" Then
'           Check rows to find next blank row
                LR = .Cells(.Rows.Count, 1).End(xlUp).Row
'               Start from Row 2 on sheet (top row header)
                If LR >= 2 Then
'                  Find number of populated rows
                   Rng = .Cells.Find("*", , , , xlByRows, xlPrevious).Row - 1
'                  Find next blank row on Consolidated Work Sheet
                   NR = Sheets("Consolidated").Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
                   If Rng > 0 Then
'                   Add Sheet data taken from tab name
                    Sheets("Consolidated").Cells(NR, 1).Resize(Rng) = .Name
'                   Add data from column 2 onwards, Rng = number of rows of data to copy, NR start row on Consolidated sheet, 1 is number of columns to copy
'                   "A2" is column of start data
'
'                   Copy "Date" information
                    Sheets("Consolidated").Cells(NR, 2).Resize(Rng, 1) = .Range("A2").Resize(Rng, 1).Value
'                   Copy "Module" information
                    Sheets("Consolidated").Cells(NR, 3).Resize(Rng, 1) = .Range("D2").Resize(Rng, 1).Value
'                   Copy "Project" information
                    Sheets("Consolidated").Cells(NR, 4).Resize(Rng, 1) = .Range("B2").Resize(Rng, 1).Value
'                   Copy "Passed" information
                    Sheets("Consolidated").Cells(NR, 5).Resize(Rng, 1) = .Range("I2").Resize(Rng, 1).Value
'                   Copy "Failed" information
                    Sheets("Consolidated").Cells(NR, 6).Resize(Rng, 1) = .Range("J2").Resize(Rng, 1).Value
                  End If
               End If
            End If
        End With
    Next
    On Error Resume Next
'   Auto fit column width to data
    .Columns("A:Z").EntireColumn.AutoFit
'   Auto fit column width to data
    .Columns("A:Z").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With


End With
End Sub

4) I would also like to add extra information on the summary sheet for columns not copied across (but based on the copied data) so that calculations and eventually a self configuring pivot table can be created. Can this be achieved easily?

This is the bit I haven't figured out yet, how to add data to a summary sheet of varying size in order to automatically build a Pivot chart.

Basically I hve a series of equipment and projects (on different tabs) that require capacity calculations. I can copy the data for each monthly requirement across from multiple projects and equipment to a summary sheet. The next step is to add the cacluation for the capacity and insert it into a pivot chart for display to show capacity of all items of equipment across multiple projects. This is the bit I don't understand how to do, other than manually after the creation of the summary sheet.

Any help on this 4th item would be welcome.