Create A summary Page

odayand · Mar 9th 2015

Hi All

I am trying to create a summary sheet based on the total of various sheets in a workbook.

Eg: Sheet 1 the word total will appear in A400 Corresponding with A400 will be amounts totaled per ageing list
Sheet 2 the word total will appear in A74 Corresponding with A74 will be amounts totaled per ageing list

The names of the sheets differ. I have found some code that works partially, however it has specific cell referencing which means that it copies random information from the sheets. I want it to create a new sheet, in A it must copy the source sheet name, in B it must copy the total ageing for that sheet.
The row of the data will change however the columns remain fixed The ageing appears from column G:L

The macro must find the word "Total" in column A and copy the corresponding cells G:L to the summary sheet into columns C:H

Code

Dim Sh As Worksheet
    Dim Newsh As Worksheet
    Dim myCell As Range
    Dim ColNum As Integer
    Dim RwNum As Long
    Dim Basebook As Workbook
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    'Delete the sheet "Summary-Sheet" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Summary-Sheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    'Add a worksheet with the name "Summary-Sheet"
    Set Basebook = ActiveWorkbook
    Set Newsh = Basebook.Worksheets.Add
    Newsh.Name = "Summary-Sheet"
    'The links to the first sheet will start in row 2
    RwNum = 1
    For Each Sh In Basebook.Worksheets
        If Sh.Name <> Newsh.Name And Sh.Visible Then
            ColNum = 1
            RwNum = RwNum + 1
            'Copy the sheet name in the A column
            Newsh.Cells(RwNum, 1).Value = Sh.Name
            For Each myCell In Sh.Range("A67,G67:L67")  '<--Change the range
                ColNum = ColNum + 1
                Newsh.Cells(RwNum, ColNum).Formula = _
                "='" & Sh.Name & "'!" & myCell.Address(False, False)
            Next myCell
        End If
    Next Sh
    Newsh.UsedRange.Columns.AutoFit
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With


End Sub

Display More

ashu1990 · Mar 9th 2015

Re: Create A summary Page

is the total row has sum of the aged columns if yes try this

Code

Option Explicit
Sub test()
Dim sh As Worksheet, ran As Range, su As Double, rw As Long
Sheets.Add before:=Sheets(Sheets.Count - Sheets.Count + 1): ActiveSheet.Name = "Summary"
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Summary" Then
    Set ran = sh.Range("A:A").Find(what:="Total", MatchCase:=False)
    
     If Not ran Is Nothing Then
     
        rw = sh.Range("A:A").Find(what:="Total", MatchCase:=False).Row
        
        su = WorksheetFunction.Sum(sh.Range("G" & rw & ":L" & rw))
        
        Worksheets("Summary").Range("A" & Range("A:A").Rows.Count).End(xlUp).Offset(1, 0).Value = sh.Name
        Worksheets("Summary").Range("B" & Range("A:A").Rows.Count).End(xlUp).Offset(1, 0).Value = su
        su = 0
    End If
End If
Next sh
Worksheets("Summary").Range("A1").Value = "Sheet Name"
Worksheets("Summary").Range("B1").Value = "Total"
End Sub

Display More

odayand · Mar 9th 2015

Re: Create A summary Page

Hi Ashu1990

Thanks for the code. The Age Totals are in the same row as the word Total.

I tried this code, it creates a new sheet labeled Summary and places the text Sheet Name in A1 and Total in B1.

Sorry but it does not seem to work.

Regards
Derryn

ashu1990 · Mar 9th 2015

Re: Create A summary Page

attach a workbook with sample data

odayand · Mar 9th 2015

Re: Create A summary Page

Hi Ashu1990

Attached is my workbook. I need the macro to:

1. Create a new Sheet and label it Summary
2. Search all other sheets for Total Per Age
3. Copy Total Per Age into Summary Sheet A1
4. Copy the source sheet name to Summary Sheet B1
5. Copy value from Column G to C1, H to D1, I to E1 etc

Your assistance is highly appreciated.

Thanks
Derryn

**royUK** · Mar 9th 2015

Re: Create A summary Page

You are using Excel 2007 or later so it would be much easier to format the data as Excel Tables. This would then make the coding simpler.

Edit: designing the data layout better would probably allow you to use a PivotTable

odayand · Mar 11th 2015

Re: Create A summary Page

Hi Roy

I know this may sound archaic but I am unable to change the layout of this document due to the legislative prescripts that govern the way we report to our government heads.

I am therefore forced to find a way to produce the required end product.

Thanks
Derryn

jindon · Mar 11th 2015

Re: Create A summary Page

See if this is kind of what you are after.

Code

Sub test()
    Dim ws As Worksheet, x, a, n As Long
    ReDim a(1 To Worksheets.Count, 1 To 2)
    For Each ws In Worksheets
        If ws.Name <> "Summary-Sheet" Then
            n = n + 1: a(n, 1) = ws.Name
            x = Application.Application.Match("Total*", ws.Columns(1), 0)
            If IsNumeric(x) Then a(n, 2) = "='" & ws.Name & "'!G" & x
        End If
    Next
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Summary-Sheet").Delete
    Application.DisplayAlerts = False
    On Error GoTo 0
    With Sheets.Add.[a1].Resize(n)
        .Columns(1).Value = a
        With .Columns(3)
            .Value = Application.Index(a, 0, 2)
            .AutoFill .Resize(, 6)
        End With
        .Parent.Columns.AutoFit
        .Parent.Name = "Summary-Sheet"
    End With
End Sub

Display More

odayand · Mar 11th 2015

Re: Create A summary Page

Hi Jindon

Thanks for submitting the code.

The code works for the most part. I have found that for some reason it only runs until row 138, so say the total appears on row 145 it does not pick up the total of 10 000 but rather the individual value of row 138. I figure that because the total appears on Row 138 on very first worksheet the code assumes that row 138 is the last line on each worksheet. If the worksheet has fewer rows than 138 it imports the total without problems but if the total appears below row 138 it imports the value from row 138.

It also creates new blank sheets 110 times which I don't need.

Thanks

Derryn

jindon · Mar 11th 2015

Re: Create A summary Page

Are you talking about the same file?

If not, no idea about what you are talking about.

odayand · Mar 11th 2015

Re: Create A summary Page

Hi Jindon

All the worksheets are in the same file(workbook)

I have attached a sample of a few worksheets to illustrate what happens after running the macro. I values which are in green should appear, the ones in red are the values from row 138.

Thanks, I hope I'm making some sense.

Regards
Derryn

jindon · Mar 11th 2015

Re: Create A summary Page

What a dirty data or you must be joking....

Code

For Each ws In Worksheets
        If ws.Name <> "Summary-Sheet" Then
            n = n + 1: a(n, 1) = ws.Name
            x = Application.Application.Match("Grand Total*", ws.Columns(1), 0)
            If IsNumeric(x) Then a(n, 2) = "='" & ws.Name & "'!G" & x - 1
        End If
    Next

And what happened to

Macro to format colums based on value

odayand · Mar 11th 2015

Re: Create A summary Page

Hi Jindon

Sorry I think when I captured the code I missed a line or something because I copies it and pasted and it worked.

Thanks for the invaluable assistance.

Regards
Derryn

Create A summary Page

Participate now!

Share