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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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....