VBA Excel: compiled & sum data based on unique values

I suspect this is possible with a pivot table rather than using VBA. Can you clarify what you mean for action 3?

See attached
EE_follow_up.xlsx

EE experts answer questions and help you to solve problems.  We aren't here to actually write custom code for you.  That is something that you may need to hire someone for if you can't do it yourself.  That isn't to say that someone with a lot of time on their hands won't jump in to your rescue.

Since Excel is not my development platform, I always struggle when I have to do something like this.  Even though I use VBA in Access every day, that doesn't make me competent with Excel VBA.  So what I do when faced with this type of problem is to fire up Excel and start the macro recorder.  Then I perform the steps I need.  After turning off the recorder, I look at the generated code and then modify it.  The macro recorder generally gives me a good start toward creating the code I need.  A proficient Excel VBA person might laugh at my kludge but as long as this isn't something so processing intensive that it needs to be efficient, I'm OK.  Laugh all you want.

Good Luck.

Hello experts,

Thank you for your feedback.
I can run a pivot table but the problem is that I need to perform this on a weekly basis and input sheet is updated daily. So the other solution is to add a button and align it with vba which run the pivot table.
The second problem concerning pivot table is that I cannot filter column field in order to take as a reference the top 5 related to the weeks in order to have a compiled view of last 5 weeks.

Saying that is it possible to have generate pivot table through VBA with the top 5 filter?

Thank you in advance for your feedback.

If you look at the file I uploaded I added a helper column which identified the last 5 weeks and used that column as a column header field in the pivot.

To update the pivot there would be a couple of actions needed.

One off - create dynamic range/table so additional data is automatically included in the pivot.

Weekly - right click on Pivot and select Refresh, latest data now in Pivot; check the column headers to ensure Blank is filtered out. This can indeed be done in a VBA routine if so required but seems a little over the top for what would only be a couple of mouse clicks to do manually.

Bear in mind that when running any VBA routine it clears the Undo history.

Thank you for your feedback.
The other reason that push me to do this through VBA is that multiple users need to generate this output view as a result I can create a command in developper tab for every user.

Changing the data to a table/dynamic range is a one off exercise.

The refresh and check of columns on the pivot can be automated with VBA if so required.

Thank you very much! I can't test it right now. I will let you know soon.

I tested and it works perfectly.
Now that there is a solution to sum based on last 5 weeks, I was wondering if we can add the following requirements:
1-Perform the sum based on unique values of column F (Who)
2-And generate Output result in different sheets based on unique values of column F (Who)

By doing so we can have the vision for each (Who) defined in Input sheet, by sheet.
I attached a dummy file.

If I need to create a new question, please let me know.
Thank you very much for your feedback.
EE_follow_up_v2.xlsm

With a pivot you can create the "Who" as a page filter and can then run a Pivot  Ption that creates a separate sheet for each "Who" filter.

I guess you'll need a routine to remove the sheets from previous runs before creating new.

The question is answered with 2 solutions, so close the question, and create a new.

Ok, I have created the new question.

It can be found at: https://www.experts-exchange.com/questions/29115333

Thank you very much for your help.