Subtotalling Columns - adapting Debra's Macro

[tony0710]

Dear All

Thanks to all the help so far - especially from Guru VBA Noob.

There's a couple of things that I need to know, in order to have my project finished.

So referring back to my thread:

http://www.excelforum.com/showthread.php?t=628177

How can I adapt Debra's macro to:

1. Attain totals of each column in each new sheet constructed by the macro?
2. Construct a new sheet with just the totals of each column from each sheet - more like a summary sheet?
3. Insert a text into the name of the new sheets constructed by the macro e.g Debra's macro makes new sheets and then names them according to the autofilter range - how can I add some text (will be the same for all sheets) after the new name? i.e: Jun 07 Exp instead of just Jun 07

If these adaptations can be done - then all my work will be done, by just using one macro - Fantastic!!!

Look forward to your replies!

Tony

[royUK]

I haven't the time to track back to see exactly what you are doing, your link doesn't help that much. If I understand correctly, I would think that a PivotTable would be better suited to what you are attempting.

[tony0710]

Thanks Roy,

But the way I have inputted the data - Pivot tables will NOT work - I have tried that path, that's why macro's are needed.

Debra's macro - done an autofilter of the original data and constructed new sheets for the range i.e: in my case, the data filtered was Months, so Debras macro autofiltered that data and constructed new sheets for each month.

However, the macro did not - give the totals for each column in the new sheets - yes I can go through all the sheets and manually do it - but surely the macro can be adapted to give the totals for each column?

Once the totals of each column have been calculated, I would then like to grab these totals and place then into a new sheet (for argument sake: called summary sheet).

Finally - Debra's macro re-names the new sheets according to the range - which in my case is the months of the year Apr 07, May 07 etc etc - so I would like to know how to adapt the macro to add "text" to the re-naming process so that the sheets are named using the range, but have the text "EXP" after the month i.e. Apr 07 Exp, May 07 Exp.

I've tried to do adapt Debra's macro - but have failed miserably!!

So help would be appreciated.

Tony

[royUK]

Can you attach an example workbook, although I really think that dsigning your data sheet correctly to allow the use of PivotTables, etc is the best way.

[tony0710]

Dear Roy

Sorry for delay - but find attached working example.

It is very basic. As you see I tried to organise data for pivot table use, but feel that I have messed up bigtime!! I wish to speed up the process - I really want to input the data (income and expense) onto one sheet and then use excel to populate other sheets.

Sheets required:

1. monthly expense and income
2. weekly expense and income
3. daily expense and income
4. daily taking = total of all departments i.e: hair, beauty etc (as this is the total money recieved on the day) MINUS (cheque total for the day PLUS credit card total for the day MINUS cash expense for the day) - I'm still unable to do this using the set-up I have!!!
5. For accountant: as above, but to hide the totals for each departments i.e: hair, beauty, etc

The macro that you find attached to Income sheet - labelled "mth" - spilts the income into the relevant months. I was then going to copy this macro to do the weekly and daily sheets for income. Then I was going to copy the macro for the expense data.

A long way around - I know - so all help will be much appreciated.

Tony