Adding a row at the end of a 'category' and entering formula for summation

[lovi]

Hello all,


I have made a spreadsheet to explain what I am trying to achieve:Total.xlsx

There is a 'Before' and 'After' Sheet

I have a set of values that belong to different categories, (See Sheet 'Before')

I need to add a row at the end of each and enter the formulae to do the sum. (See Sheet 'After')


Can you guys help me with the vba code to do so?

Thanks in advance

Lovi

[benishiryo]

hi Lovi. i'm not good in VBA, so i used some tricks & recorded the following:

Please Login or Register  to view this content.

basically i:
1) added headers
2) did a Subtotal function
3) Ungroup the list from Subtotal
4) Clear the contents for the headers & a Grand Total from Subtotal
5) Find "* Total" & replace with "Total", because my Subtotal will give "A Total", "B Total", etc

there's definitely going to be a better code than this, but you can use this if nothing comes up.
=)

[Winon]

@ benishiryo,

Not bad at all.

You could shorten it to:

Please Login or Register  to view this content.