VBA to group totals of each category within each date range

I know this can be accomplished with a pivot table. However, there will be different people doing this task each day who cannot grasp the concept of a pivot table. So this has to be an automated process through VBA to prevent human errors.

The data is on a spreadsheet named SUMMARY.
The data was filtered in ascending order by Date (column G) then by Category (column F).

What I need help with, is to create a matrix for each date that will indicate the total amount for each category within that date.
The only criteria for totaling any category is whether or not an X is placed in column E (the 'EACH' header)
Column D can be ignored at this time (but not deleted)

This spreadsheet is never more than 100 rows, so looping through the rows is OK, unless someone could do this with filtering. I don't know how to do either, and I looked for hours to find a method. I need help on this one. So, I hope someone can assist me.

Here's an idea I have about the desired layout result, but maybe someone has a better layout idea.

1-Dec
BLACK 30
BROWN 21
BLUE 5
GOLD 44
GREEN 21
RED 25

2-Dec
BROWN 43
GOLD 25
RED 27

3-Dec
BLACK 4
BROWN 40
GREEN 10
RED 36

The file is attached below, and the desired result is also on the Summary sheet. However, it does not need to be there. It can be on a separate sheet.
Thank you for looking at this.

What about a Pivot table and a macro to refresh it: See attached

Thanks PCI, I didn't know pivot tables could be refreshed with a macro.
That's good to know.

The only concern I have with this approach is that this report will not be used by me, or anyone else that knows how to create pivot tables. So, I can't be inserting pivot tables each time this report is run, nor train someone else to do it because no one else knows Excel beyond using it as a data entry log. That is why I need this part to be done at the click of a button. Because that's about all anyone else in my company can do.

I was able to find the code below that is close to what I need. However, the code counts the number of instances that the products' name appears in column F and puts the result in column R.
If this code could be enhanced to add the quantities from column C every time an 'X' appears in column E, that would be perfect.

Thanks,

Please Login or Register  to view this content.

See your macro with ajustment

Please Login or Register  to view this content.

karedog, PCI, jindon,

Each of you provided the perfect solution for what I need.

Thank you all. This is much appreciated. Each of you are a genius!

Reps have been provided to each of you.



PCI,
Your ability to modify the code I found, and make it work for my purposes, is exceptional. That will help me see and understand the logic of the code.
I spent a whole day trying to modify that code, before I asked for this help.

Again, to each of you ... Thank you so much.

Hi Jim885,
Thank you for your thanks.
It is a pleasure to know that you spent time to try, be free to ask for explanation.
Now if you want to progress in the use on the Scripting Dictionary, no doubt Jindon's code is smarter
PCI