VBA for Autosum up on multiple cells

[moonoo]

Dear excel experts i need very urgent help on my file. This file is done every month end. Here is the story. i receive a raw data extracted from accounting software and i do formating on it. Reapplying sums, coloring, bordering.... etc. My problem is when i record a macro to autosum up all of my "sub" categories. Excel just captures static addresses. I want to make this sum range dynamic. because every month record numbers vary. please have a look at my attached file. I looked for a solution for this matter for whole night couldn't find anything. Can be there any easy way to do it automatically, dynamicaly?

[JBeaucaire]

Your sample workbook appears to be showing your final result. Why not post your macro and an unformatted raw sheet so we can see what you do, then help adjust your actual macro to add they dynamics you want?

[moonoo]

Thank you for your reply. Actually this not the final results, if you see cells highlighted in red, they supposed to have Subtotal function which covers its above records.
Now how i do is, i filter my worksheet J column by "Sub" and apply autosum on those SUM cells manually. if there are over 1000 "Sub" elements, it's done manually. Attached files are source (need to be formatted) and another one is my macro.which does until this "Sub" thing.. thank you.

[jaslake]

Hi moonoo

I'm a little confused here. Your original file you posted "Formatting.xlsx" looks nothing like the most recent file "123465.xls". Which file is it that we should be working on?

John

[jaslake]

Hi moonoo

This code appears to work on your original file "Formatting.xlsx"

Please Login or Register  to view this content.

You'll need to save it as a Macro Enabled file (xlsm).

John

[moonoo]

Hello John,

Thank you for you time.
I tested your code and it selects my C columnt instead of H. which holds my transaction amounts.

I've one idea. i'm not sure whether this is possible or not. The concept is, since i can filter and select only Subtotal cells. Can you do simple code to detect my auto sum range (like excel auto sum guess) above a single Subtotal cell, and SUM UP then shift down to next subtotal cell and search and sum up again etc,. It loops through until it finds no more used cell below last one. How does it sound? is it possible.


Thanks
Moonoo

[moonoo]

One more thing on my last uploaded sample files. You just delete some "IN" or "CJ" from A column and respective records in B column of 123465.xls and select whole worksheet n COPY, PASTE to New Macro.xlsm and run MASTER macro from macro list. which i've managed to record so far.
The problem is i cannot record this Excel AUTOSUM action in Macro dynamically. when i record it just captures static cell addresses.

tks,
Moonoo

[jaslake]

Hi moonoo

It's my understanding that you want formulas in the Red Cells in column H of the attached file. The code in the attached workbook does just that on my platform.

The code indeed works off of column C but only for purposes of finding "areas" that fit into a subtotal. Run the code...check the Red Cells. Formulas should be there.

If the procedure doesn't do what you require, let me know.

We can discuss your

last uploaded sample files

once I'm certain I understand your needs on your first uploaded file.

John

[moonoo]

Oh, god. Thanks John, It works like charm. Hurray. Thank you soooo much.

Big fan.

Moonoo
\o/