vba to sort group copy paste to another sheet
 

dear all, need help from the forum.
if i want to sum the same material in (column c) together with qty (column
d) and price (column o) and put in new sheet using vba. i want to automate by
click on button and put in another sheet.
i need to sort and group and sum up the price and qty by different material.

my problem now is
1)starting row could be different. 1 time is row 15 but other time could be
20, etc.
2) in between row sometimes there is a blank row and sometimes no blank row.
3)how to paste special by value automately
4)columns no change. which means i only make use of column c, d, o
5) how to know which row to start and end at which row as in between having
empty rows.

format something like this. in between item sometimes there is a blank row
or sometimes no blank row. and also i need to sort and group as material not
in sequence. starting row also can vary. sometimes at row 15 other time could
be 18.

item desc material qty price
1 head k123 3 45
blank row
2 shell k123 5 50
blank row
3 stiff k234 9 35
4 giant k567 4 60
5 shell k234 7 30
blank row
6 shell k123 5 60