subtotal macro

If you can upload a sample sheet

It would be better

Regards

Are you looking to sort, subtotal, or maybe both?

Originally Posted by mahju

If you can upload a sample sheet

It would be better

Regards

Here is a sample. Please note...the number of rows will contunue to increase as the year progresses....

Originally Posted by bentleybob

Are you looking to sort, subtotal, or maybe both?

Both....both sort and subtotal....thanks for reminding me of that detail !

Hi
I have created a micro

Check and see if it works for you

See attached file

Regards

Yes... it is almost there.....(Thanks so much for the macro to clear the subtotals!!)

however...

1) there may need to be a "tweak' to the code. When data is entered into a new row (in #31+) the sort by date does not occur in the macros.

2) Also.....as the database gets larger, I will need to "filter in" only certain rows based on a range of dates. And....the filter will vary on if I'm using column G (Liability) of column H (Deposit).

In other words....in the attached file some of the dates in the rows are Jan while others are Feb. When I am seeking the subtotal for Liability I will want to see all the dates between 1/1 & 3/31. However when seeking the subtotals for Deposit I will want to only see 1/1 - 1/29. Is there a way to do this via the macro?

please note, the date ranges will change based on when the data is being viewed, so somehow the user would have to have the ability to key in the desired date range...

Hi
Here is the solution

Please note that place of data and other information should not be changed as they are referenced in macros.

If you change them change the code accordingly

I have used advanced filter , sort and subtotal commands.

I have also extended the range up to 50000.

Please check if it works for you

See attached sheet

Regards

Looking GREAT! Just one more item.....i am unable to view the outline symbols on the far left of the workbook. Once the data is filter/sorted/subtotals via the macros, I wish to only be able to few the subtotals lines.

My excel settings are set to allow outlines to be displayed. I also tried <Crtl 8> and get the following message

"Cannot show the outline symbols because no outline exists in the active workbook"

Any suggestions?

Sorry for inconvenience.

I have applied automatic outline now.

But the subtotals are not in bold now.

Dose this ok?

See attached sheet

Regards

The outline is now visible....but does not seem to be working properly.

Bolding is not an issue. Did it work for u?

Thank you. yes- it appears to be working. I would like to have the subtotals copied to a second worksheet. This second worksheet will then be used to generate a final worksheet that will link back to each subtotal line.

I attempted to create a macro to have the subtotal rows copied to a new sheet.....but it doesn't seem to work well. It is looks disorganized. Also when I clear the subtotals on sheet 1 and then subtotal again, the data on the second second is not overwritten but rather added on as additional lines.

Is there a better way to get the subtotal rows on sheet 1 copied to sheet 2? and refreshed each time the totals in sheet one change?

Thanks so much for your expertise!

Thanks

Your file does not seem to work on me (using excel 2003).

However I have changed the macro to move filtered data to new sheet and then apply subtotal there.

Change "Cut" to "copy" if you want.

Think this should work

Regards

And if you want to copy filtered records to sheet 2 and remains subtotal on the sheet1 then use this version

Clear button will clear subtotal as well as data on sheet 2

Regards

Thank you. Yes, i want the data to remain in both sheet 1 & sheet 2. So your version 2 file is the preferred. The copy works as well as the clear...the only problem I have is that it is the inidividual lines that are copying over, rather than just the subtotals.

Should there something in the macro that needs to direct it to only copy/paste the visisble cells (these would be the subtotals)?

Thanks in advance for ALL your HELP!

On the second sheet subtotal can also be applied.

But it will be predefined level (outline 1,2 or 3 or may be a value in a cell) Please tell at at which level you want subtotal to show values

Thanks

Regards

Sheet 2 subtotal is most useful at outline 2. Will sheet 2 be able to accomdate the filters selected in sheet 1?

mahju -

my request for the values in Sheet 2 to be at outline/level 2 is because outline 2 is where the subtotals occur for each change in date.

The subtotals of each change in date (liability or deposit) are what I are striving for...... Which you have been successful in helping me with. If it is possbile to copy/paste/values of just the subtotal vlaues to sheet 2, I will be able to then prepare a seperate summary that links back to specific cells on sheet two.

thanks for your patience.

Sorry for the delay

Thankful for your explanation and patience

I am working on your file and it will be completed in 1 to 2 days

All the subtotal rows will be copied to the sheet 2 when a button on sheet2 is pressed You will be able to do any thing with this data.



Regards

I have created a new macro to copy only subtotal rows to sheet2

After subtotal click "copy subtotal" Button on sheet2 and enjoy

This is independent to subtotal outline in sheet1.

It always copy subtotal rows to sheet 2

Thanks for your patience


Regards