Creating auto updating Summary Sheet

[baldymon]

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

[baldymon]

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

[baldymon]

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.

[baldymon]

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.