Make a summary of three sheets in one sheet

Hi ,

I need a help to make a summary of 3 sheets Seg1 Seg2 Seg3 in other sheet named Summary.
when making a summary sheet I need to keep the formatting of the main 3 sheets .
In the Summary sheet the zones should be listed horizontally as clearly explained in the attached Sample file.

please note the seg sheets could contain over 100 of rows and could contain more zones.




please help , this is a daily task for me.

much appreciated.

sorry for that , I updated to xl2013.

many thanks for the reply.
this is not what im looking for.
i want the result to look exactly as in the summary sheet attached.

the second thing, im using my workplace PC and i cannt use power query.

please help with a VBA code.

According to the attachment a VBA demonstration as a beginner starter to paste to the Summary worksheet module :

PHP Code: 

Sub Demo1()
        Cells.Clear
        Columns.ColumnWidth = StandardWidth
        Columns.Hidden = False
        Application.ScreenUpdating = False
        R& = 2
    For S% = 1 To 3
        With Sheets(S).UsedRange
               .Columns(4).AdvancedFilter 2, , [A2], True
                V = [A2].CurrentRegion
            For L% = 2 To UBound(V)
                    Z$ = "Zone " & V(L, 1)
                    C = Application.Match(Z, UsedRange.Rows(1), 0)
                If IsError(C) Then
                         C = 3 + N% * 7
                         N = N + 1
                    With Cells(C).Resize(, 5)
                        .HorizontalAlignment = xlCenterAcrossSelection
                        .Interior.Color = vbYellow
                        .Cells(1).Value2 = Z
                    End With
                End If
                     Cells(R, C).Value2 = .Parent.Name
                     [A3].Value2 = V(L, 1)
                    .AdvancedFilter 2, [A2:A3], Cells(R + 2, C)
                With Cells(R + Cells(R + 2, C).CurrentRegion.Rows.Count + 2, C + 4)
                    .Font.Color = vbRed
                    .HorizontalAlignment = xlCenter
                    .Formula = "=SUM(" & Range(Cells(R + 3, .Column), .Cells(0)).Address & ")"
                     Cells(R, C + 1).Value2 = .Value2
                End With
                With Cells(R, C).Resize(, 2)
                    .Font.Bold = True
                    .HorizontalAlignment = xlCenter
                End With
            Next
        End With
            R = UsedRange.Rows.Count + 2
    Next
        [A2].CurrentRegion.Clear
        Application.ScreenUpdating = True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

this works like a charm , thank a million MAN.
but please some points to clarify more :
- can you comment the code.
- what version of excel can i apply this code.
- if I want to add more columns to seg sheets , what needs to be changed in the code.
- if i want to filter by branch not by zones , what needs to be changed in the code.
please help again,
thanks in advance.

it really rocks !

My revamped demonstration where you just have to amend the constant F to filter by another column (try 2)
and nothin' to change in case of more columns :

PHP Code: 

Sub Demo1r()
  Const F = 4
        Cells.Clear
        Columns.ColumnWidth = StandardWidth
        Columns.Hidden = False
        Application.ScreenUpdating = False
        R& = 2
    For S% = 1 To 3
        With Sheets(S).UsedRange
                If S = 1 Then B$ = .Cells(F).Text & " ": K% = .Columns.Count: M% = K + 2
               .Columns(F).AdvancedFilter 2, , [A2], True
                V = [A2].CurrentRegion
            For L% = 2 To UBound(V)
                    Z$ = B & V(L, 1)
                    C = Application.Match(Z, UsedRange.Rows(1), 0)
                If IsError(C) Then
                         C = 3 + N% * M
                         N = N + 1
                    With Cells(C).Resize(, K)
                        .HorizontalAlignment = xlCenterAcrossSelection
                        .Interior.Color = vbYellow
                        .Cells(1).Value2 = Z
                    End With
                End If
                     Cells(R, C).Value2 = .Parent.Name
                     [A3].Value2 = V(L, 1)
                    .AdvancedFilter 2, [A2:A3], Cells(R + 2, C)
                With Cells(R + Cells(R + 2, C).CurrentRegion.Rows.Count + 2, C + 4)
                    .Font.Color = vbRed
                    .HorizontalAlignment = xlCenter
                    .Formula = "=SUM(" & Range(Cells(R + 3, .Column), .Cells(0)).Address & ")"
                     Cells(R, C + 1).Value2 = .Value2
                End With
                With Cells(R, C).Resize(, 2)
                    .Font.Bold = True
                    .HorizontalAlignment = xlCenter
                End With
            Next
        End With
            R = UsedRange.Rows.Count + 2
    Next
        [A2].CurrentRegion.Clear
        Application.ScreenUpdating = True
End Sub 


This is perfect , it helps me alot.
can this be modified so there will be fixed number of empty rows between tables , say one empty row only.

thanks in advance.

Nothing to change as this is already the case …

Anyway you can mod the calculation of the variable R.