Subtotal VBA

[Wayne085]

Hi there,

I have a macro that formats a spreadsheet. To finish it off I need code that does the following.

1) Adds subtotals and a grand total, in bold.

2) Inserts a blank row after each subtotal.

3) After the macro has run and the spreadsheet is formatted, any change to the "Amount" column will also update the subtotal and grand total.

Can anyone help please?

Thnak you.

Item PO Date Amount
PEMS2714 3000086401 24/06/2008 10.00
PEMT6781 3000087313 10/07/2008 15.00
67679 3000087313 10/07/2008 20.00
Jul-08 45.00

PEMV8998 ST17893 6/08/2008 20.00
AMAL2773 3000093472 14/08/2008 30.00
PEMZ8397 3000096172 23/09/2008 40.00
Sep-08 90.00

PENA4834 3000098208 1/10/2008 5.00
PENA5583 3000098772 2/10/2008 25.00
PENA6627 3000098884 3/10/2008 50.00
PENA9688 3000097272 7/10/2008 100.00
Oct-08 180.00

PEND2968 3000100872 3/11/2008 50.00
PEND2967 3000100872 3/11/2008 150.00
Nov-08 200.00

Total 515.00

[royUK]

Why not use a PivotTable?

[Bob Phillips]

Please Login or Register  to view this content.

[Wayne085]

Hi Bob,

Thank you for this piece of code.

I inserted it into my macro but an error occurs at the following line.

Please Login or Register  to view this content.

Do you have any suggestions? I could upload the full macro & a source document if that would help?

Thanks again for all you have done.

[royUK]

It would help if you said what error, attaching the workbook would probably help

[Wayne085]

Hi Roy,

Attached is the macro and a source file (PS.xls) that the macro formats.

Thanks again.

[Bob Phillips]

Seeing as you only gave a schematic of the data and didn't give the exact layout, you needed to adjust the code to the real columns.

Please Login or Register  to view this content.

[Wayne085]

Hi Bob,

Thanks for what you have done, I really appreciate it.

I have tried the code but with my limited ability I am unable to adjust it to fit the actual columns. Attached is a file that shows the layout once the bulk of the formatting has been done.

Would you be kind enough to adapt this code?

Thanks again.