Subtotal according to changing number of columns

[josnah]

Hi I need help with the subtotal code:

Please see attached image to understand how my table will look like for a full year.

I need to produce YTD tables where not all months will be included.
For Jan YTD, my code will look like this:

Please Login or Register  to view this content.

For Jun YTD, my code will look like this:

Please Login or Register  to view this content.

and for Dec YTD, my code will look like this:

Please Login or Register  to view this content.

How can i automate this instead of changing the array every month?
Any help is greatly appreciated!

[Leith Ross]

Hello josnah,

The easiest way to automate this would be to first create 3 separate macros in a Standard VBA module and then add 3 command buttons to your worksheet. Assign one macro to one button. Then you can click the button you want to display the required YTD totals.

Sincerely,
Leith Ross

[josnah]

hi Leith,

thanks for the suggestion. but then i'll need to create 12 seperate macros & commandbuttons for each month of the year.

but i was looking at sth more like...
if the 3rd last column is a particular month, then the array should change accordingly as listed below.

Month ArrayList
Jan 3,4
Feb 3,4,5
Mar 3,4,5,6
Apr 3,4,5,6,7
May 3,4,5,6,7,8
Jun 3,4,5,6,7,8,9
Jul 3,4,5,6,7,8,9,10
Aug 3,4,5,6,7,8,9,10,11
Sep 3,4,5,6,7,8,9,10,11,12
Oct 3,4,5,6,7,8,9,10,11,12,13
Nov 3,4,5,6,7,8,9,10,11,12,13,14
Dec 3,4,5,6,7,8,9,10,11,12,13,14,15

[Leith Ross]

Hello josnah,

This macro can be attached to a single button. It will prompt you for the month and then perform the YTD calculation.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[josnah]

Hi Leith,

Many thanks for your post. That works for me...
I'm beginning to understand how to use Select Case now!

Josnah