An automation to create summary from change month

[PRADEEPB270]

I have a file wth around 125000 rows.But here,I have taken a simple example.

Actually,I want to apply an automation working in my attach file.It is possible thru VBA codes but have no idea to create.

When I change the month ( Cell No.O1 ) then,automatic create the unique 'Group' and sumup according to 'Group' wise 'Qty','Sales Amount','Freight Amount' and 'Insp.Charges Amount' accordingly to the month.

For better understanding,I have plot an example of a month i.e.Jan-2014.

Please provide the VBA codes workings in this context.

[HansV]

Column B doesn't contain date values, but text values that look like dates. This makes it very difficult to process the data.

See the screenshot below. Real date values would have been right-aligned instead of left-aligned. (And no, changing the horizontal alignment won't help)

S0577.png

[PRADEEPB270]

Now,please look the revised attach file.I have changed the 'Col.B'.

[HansV]

You can use a pivot table for this, no code needed. When you select a different month from the dropdown, the pivot table will be updated automatically.
See the attached version.

Datas.xlsx

[PRADEEPB270]

But,if my column B is in date format 01/01/2014 ( dd/mm/yyyy) instead of Jan-14,and there are so many entries in the month like 02/01/2014,06/01/2014 etc.In that case,pivot table will not depict the result month wise ( Jan-14).It will show date wise.How can we see the result of different dates in a month wise e.g.Jan-14?

[HansV]

You can add a calculated column named Month with formulas such as =B7-DAY(B7)+1, and use that in the pivot table.

Datas.xlsx

[Rudi]

Hi Pradeep,

Here is the VBA code for the request you posted.

Just for clarity though, I see you have rejected the use of Pivot Tables a few times in the past. Can I ask why you do not use them as they are very easy to set up and is 100% more flexible and dynamic than VBA?

Datas.xlsm

[PRADEEPB270]

Thanks Rudi,for your awesome support to me.Very glad to find the result from Pivot table and VBA codes.