Subtotals to move to next column

[billy006]

I am using Office 2010. I have some data on a spreadsheet (see attached picture).

As you can see i need to seperate the transactions using "Account Titles".. I first sort out the data so all "account Titles" club together then I am using the Subtotal function in Data tab.. What it does is add a row after each total of "Account Title" and throws a subtotal in the "Gross" column, I then have to move each Subtotaled amount to the "Subtotal" column..

Is there a way that it automatically moves the subtotal of each to the "Subtotal" column rather than me moving it from "Gross" column.12.jpg

[Aardigspook]

Welcome to the forum.

I assume that you have a LOT of data so don't want to do it all manually. There's no formula-driven way to do this, but you can do it with this macro code:

Please Login or Register  to view this content.

For this to work, you must select a range which includes all the rows you want and it must include column G (Gross). Using the example you posted a pic of, you could select only G35:G50 and it will work for the three subtotals shown in the pic - but not any below them. Note that it will move the grand total (right at the bottom, not shown in your pic) too.

If you haven't used macros before you'll need to go to:
File - Options - Trust Center - Trust Center Settings - Macro Settings - select the second option down (disable all macros with notification) (you could use the fourth - enable all - but that's not recommended - this way you get to choose when/if to allow macros to run).
Then:
- Copy the code above to the clipboard
- Open your Workbook
- Press ALT + F11 to open the Visual Basic Editor.
- Select 'Module' from the Insert menu
- Type 'Option Explicit' (without the inverted commas) then paste the code under it
- Press ALT + Q to close the VBEditor

To run the macro, press ALT + F8 then select the macro name (OffsetSubtotals) and click 'Run'.
You will need to save the file as a Macro-Enabled Workbook (.xlsm).

Hope that helps.

[billy006]

Thanks for your help. I will try that today.

Yes you are right, i have a lot of data to do a subtotal of and I did not think it would be so complicated haha but yeah thanks again, i will try it today.