Creating a summary table that has totals

VBA solution would be ideal. Thank you.

What I mean by "30 different sheets" is that I am provided with a data table everyday that contain amounts (for that day). So for a month like September, I would have 30 such tables of data (one for each day). Each table will contain the numbers for whatever amounts were gathered that day.

The macros I created assembles all that data on one sheet. All I do is click on a button, and the macros prompts to open a file from my desktop. I select a workbook and the macros will open it, copy the data, and paste the data onto one sheet. I do this for each daily table file and so by the end, I will have a stack of all of them in one worksheet. What I need now is to add all that information into one table. The table would have the totals for the month.

I figured in the example I provided, if I could do it with two tables, I would just tweak/add code in order to do it for 30 tables (or however many I had for that month).

I created those tables as examples, and they reflect the construction of the data exactly. The data I am actually using is company sensitive so I can't provide it.

The macros I am using to open and consolidate the tables into one worksheet is as follows:

Sub CopyPaste_New_Daily_Data()
'
'
Dim ReportWbk As Workbook 'workbook with report data
Dim Report As String 'name of file with report data
'
Application.FileDialog(msoFileDialogFilePicker).Show
Report = Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
Set ReportWbk = Workbooks.Open(Report)
'
ReportWbk.Sheets(1).Range("A1").Select
ActiveCell.Range("A1:P28").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'
ThisWorkbook.Sheets("Data").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
'
ReportWbk.Close (False)
ThisWorkbook.Sheets("Button").Activate
'
'
End Sub

- J

Try the attached

Sub test()
    Dim r As Range, a, i As Long, ii As Long, w
    Dim txt As String, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    For Each r In Columns(1).SpecialCells(2).Areas
        If Not r.MergeCells Then
            a = r.CurrentRegion.Value
            ReDim w(1 To UBound(a, 2))
            If Not dic.exists("Summary") Then
                w(1) = Format$(CDate(Right$([a1], 11)) _
                , """Summary for Month of"" mmmm")
                dic("summary1") = w: w(1) = Empty
                dic("summary2") = w
                For ii = 1 To UBound(a, 2)
                    w(ii) = a(1, ii)
                Next
                dic("summary3") = w
            End If
            For i = 2 To UBound(a, 1)
                txt = Join(Array(a(i, 1), a(i, 2)), Chr(2))
                If Not dic.exists(txt) Then
                    ReDim w(1 To UBound(a, 2))
                    w(1) = a(i, 1): w(2) = a(i, 2)
                Else
                    w = dic(txt)
                End If
                For ii = 3 To UBound(a, 2)
                    w(ii) = w(ii) + a(i, ii)
                Next
                dic(txt) = w
            Next
        End If
    Next
    With [h1].Resize(dic.Count, UBound(a, 2))
        .CurrentRegion.EntireColumn.Clear
        .Value = Application.Index(dic.items, 0, 0)
        With .Rows("1:3")
            .Rows(1).HorizontalAlignment = 7
            .Interior.Color = 10213316
            .Font.Bold = True
            .Rows(1).BorderAround Weight:=2
            .Rows(2).BorderAround Weight:=2
        End With
        With .Offset(2)
            .Resize(.Rows.Count - 2).Borders.Weight = 2
            .Columns.AutoFit
        End With
    End With
End Sub

Jindon - Thank you! The code works well. I will try to apply it so that it will do so with the rest of the daily tables included.

YasserKhalil - Thank you for your suggestions and efforts. Much appreciated.

- J

Mr. Jindon I tried your attachment and I encountered an error at this line

                w(1) = Format$(CDate(Right$([a1], 11)), """Summary for Month of"" mmmm")

Can not replicate the error here.

Originally Posted by jindon

Can not replicate the error here.

I didn't get what you mean by this sentence ..
Do you mean that this line can't be an error!! or you mean I don't have to post errors in this thread?

Means runs without error, so I can not replicate the error that you are saying here.

Mr. Jindon English is not my native language and I didn't recognize what the word (replicate ) means ..
Do you mean that there is no error when you run this macro??
Is there a library reference to be added? > May be regional settings may cause the error

I think it's a matter of date format for regional setting, but I don't know why you are getting error.

Dates are trouble anyway.

What's the suitable format in regional setting for dates to avoid that error?

No idea, and I don't want to touch to my Regional Settings....