VBA code to get totals

[Mark Green]

I wonder if someone can help me with some VBA code to get totals?

I will be able to write code to sort the columns according to column B (Description).

I would like to first insert a row at the end of each Col B category.
Then have the totals of all Col C (debit) and D (credit) calculated for each category.
Finally I'd like to take these totals and have them summarized in another part of the sheet.

So it looks something like this now:

[TABLE="width: 531"]
<tbody>[TR]
[TD="align: right"]9/19/2017[/TD]
[TD]TRIANGLE SEWING[/TD]
[TD="align: right"]5.85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/2/2017[/TD]
[TD]TRIANGLE SEWING[/TD]
[TD="align: right"]7.16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/16/2017[/TD]
[TD]TRIANGLE SEWING[/TD]
[TD="align: right"]6.77[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2017[/TD]
[TD]TRIANGLE SEWING[/TD]
[TD="align: right"]17.50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD]UBER   *US    8.05_V[/TD]
[TD="align: right"]11.15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD]UBER   *US    9.78_V[/TD]
[TD="align: right"]13.54[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD]UBER   *US   13.14_V[/TD]
[TD="align: right"]18.19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD]UBER   *US   17.02_V[/TD]
[TD="align: right"]23.58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD]UBER   *US   21.87_V[/TD]
[TD="align: right"]30.29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/3/2017[/TD]
[TD]UBER DO FE  185.82_V[/TD]
[TD="align: right"]5.35[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/6/2017[/TD]
[TD]UBER DO FE  185.82_V[/TD]
[TD][/TD]
[TD="align: right"]5.06[/TD]
[/TR]
[TR]
[TD="align: right"]4/20/2017[/TD]
[TD]UNION GAS    H7K3R3[/TD]
[TD="align: right"]138.67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3/20/2017[/TD]
[TD]UNION GAS    K3R9J5[/TD]
[TD="align: right"]150.90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2017[/TD]
[TD]UNION GAS    K4X3W6[/TD]
[TD="align: right"]157.96[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9/18/2017[/TD]
[TD]UNION GAS    Q3Z6H4[/TD]
[TD="align: right"]32.45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6/14/2017[/TD]
[TD]UNION GAS    R3U5L3[/TD]
[TD="align: right"]72.70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/16/2017[/TD]
[TD]UNION GAS    W8R2H3[/TD]
[TD="align: right"]116.03[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5/16/2017[/TD]
[TD]UNION GAS    W8W5H4[/TD]
[TD="align: right"]109.81[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/11/2017[/TD]
[TD]UNION GAS    X4R5A8[/TD]
[TD="align: right"]36.29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/17/2017[/TD]
[TD]UNION GAS    Z4H5W7[/TD]
[TD="align: right"]38.86[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD]UNIVERSAL    10.64_V[/TD]
[TD="align: right"]14.74[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD]UNIVERSAL    34.07_V[/TD]
[TD="align: right"]47.18[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Would like totals to appear for each category and also in another place like:

[TABLE="width: 256"]
<tbody>[TR]
[TD="colspan: 2"]TRIANGLE SEWING[/TD]
[TD="align: right"]85.02[/TD]
[TD="align: right"]13.55[/TD]
[/TR]
[TR]
[TD]UBER[/TD]
[TD][/TD]
[TD="align: right"]102.10[/TD]
[TD="align: right"]5.06[/TD]
[/TR]
[TR]
[TD="colspan: 2"]UNION GAS[/TD]
[TD="align: right"]853.67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]UNIVERSAL[/TD]
[TD="align: right"]61.92[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciated!

 

[Dryver14]

This can all be done on the subtotals on the data tab

 

[Mark Green]

This can all be done on the subtotals on the data tab

Thanks very much Dryver! I never knew about this built-in function.

That solves half my problem.
Any thoughts on how to copy these totals to another place
on the sheet for easier reading?

 

[Mark Green]

Another problem I am now having is that Excel is too fussy with the Description field.
Entries might be

[TABLE="width: 270"]
<tbody>[TR]
[TD]BELL CANADA  A2L7Z6[/TD]
[/TR]
[TR]
[TD]BELL CANADA  A6J8U3[/TD]
[/TR]
[TR]
[TD]BELL CANADA  H7K3Q8[/TD]
[/TR]
[TR]
[TD]BELL CANADA  K3R9J2[/TD]
[/TR]
[TR]
[TD]BELL CANADA  K4X3W2[/TD]
[/TR]
[TR]
[TD]BELL CANADA  R2R4W8[/TD]
[/TR]
[TR]
[TD]BELL CANADA  R3U5K8[/TD]
[/TR]
[TR]
[TD]BELL CANADA  W8R2A7[/TD]
[/TR]
[TR]
[TD]BELL CANADA  W8W5A9[/TD]
[/TR]
[TR]
[TD]BELL CANADA  Y8W3L6[/TD]
[/TR]
</tbody>[/TABLE]

and this will result in dozens of sub-totals, when they should only be for BELL CANADA.
Any way to easily restrict this to the main name only?

 

[Dryver14]

firstly regarding Bell Canada, when you select subtotals it gives you the option on which column to sort on, is there another column that does not have this problem, if so use that

secondly I would probably suggest inserting another column to mover the serial number into

thirdly If you click on any of the numbers that appear on the left hand side you can collapse the heading as you see fit.

Subtotals allows you to copy and paste without the need to select visible cells only

 

[Mark Green]

firstly regarding Bell Canada, when you select subtotals it gives you the option on which column to sort on, is there another column that does not have this problem, if so use that

Well, there aren't too many columns. Date, Descrip, Debit, Credit.
Descrip is the only possible one.

secondly I would probably suggest inserting another column to mover the serial number into

Yes this would make sense. But I would still need to somehow parse the serial number from the Descrip first.

thirdly If you click on any of the numbers that appear on the left hand side you can collapse the heading as you see fit.

This wouldn't help. As Excel would still sort each Descrip into dozens of sub-headings when only one is wanted.
Don't need Bell 1, Bell 2, Bell 3, etc. Just Bell.

Subtotals allows you to copy and paste without the need to select visible cells only

I'm trying to come up with something in VBA where I wouldn't need to do any manual copying and pasting.
Just have Excel copy Descrip. and sub-totals to another area of sheet.