Creating auto updating Summary Sheet

Hi, first post, so hopefully have adhered to all forum rules.

I have been looking to create a summary sheet from data that exists in other sheets (within the same workbook). Searched and searched and came across a Excel Forum thread which had the following macro in a book1.xls file: -

Private Sub Worksheet_Activate()
Dim Sheet As Worksheet
For Each Sheet In Me.Parent.Sheets
    If Sheet.Name <> Me.Name Then
        If Sheet.Cells(Rows.Count, 1).End(xlUp).Row <> 1 Then
            Sheet.Range(Sheet.Cells(2, 1), Sheet.Cells(Sheet.Cells(Rows.Count, 1).End(xlUp).Row, 10)).Copy Destination:=Me.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

        End If
    Else
        Me.Range(Cells(2, 1), Cells(Rows.Count, 10)).Clear
    End If
Next Sheet
End Sub

This macro seemed to perform the job I needed as each tab (varying number of tabs) has a list (in the same format) but differing lengths and this macro seemed to append to the data as it went throug the tabs, and the summary table was updated automatically as data was populated on the other tabs, or as tabs were inserted. It seemed to only work for the first 10 columns.

I thought it was ideal and copied it down without noting the thread number or the title.

As a novice, what I also failed to find out was: -

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)
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?
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?

Not much to ask, I know but if anyone can help, it would be appreciated.

Thanks