Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jun 2008
    Location
    Cork, Ireland, Republic of
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subtotal in a Macro (2003)

    Hi, I was wondering if anyone could tell me if it's possible to subtotal 4 columns on an Excel sheet using a Macro? Every month I have to subtotal several sheets in 1 file. Each sheet is subtotaled using the same columns : C, D, E, F, and G.

    Thanks
    Sheila

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Subtotal in a Macro (2003)

    I think what you want to do is possible even though I am unsure wxactly waht you want to do...

    Could you elaborate on what you have and what you want? Attaching a sample file (remove any proprietary info) would be helpful, perhaps sheets representing "before" and "after"

    Steve

  3. #3
    Lounger
    Join Date
    Jun 2008
    Location
    Cork, Ireland, Republic of
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal in a Macro (2003)

    Hi Steve

    apologies for delay in responding. Please find attached samples of what i require.... this is only 1 of several reports that are initially sorted by Section, Department, and O_Type. Then it's subtotaled by Section, Sum and cols K, L, M, N and AC - this is the part that would be great if a macro would perform the subtotal?

    Thanks

    Sheila
    Attached Files Attached Files

  4. #4
    Lounger
    Join Date
    Jun 2008
    Location
    Cork, Ireland, Republic of
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal in a Macro (2003)

    and here is the desired outcome file - i had to make them much smaller in size to load these files - they are usually 5 times the size of this example.

    TIA

    Sheila
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtotal in a Macro (2003)

    I don't see anything in column AC, so I omitted it from the macro.

    Here is a slighty edited version of a recorded macro:
    <code>
    Sub CreateSubtotals()
    With Range("A1").CurrentRegion
    ' Remove existing subtotals, if any
    .RemoveSubtotal
    ' Sort the range on columns R, Q and I
    .Sort Key1:=Range("R2"), Key2:=Range("Q2"), _
    Key3:=Range("I2"), Header:=xlYes
    ' Add subtotals to columns K, L, M and N, grouping on column R
    .Subtotal GroupBy:=18, Function:=xlSum, _
    TotalList:=Array(11, 12, 13, 14)
    End With
    End Sub</code>

  6. #6
    Lounger
    Join Date
    Jun 2008
    Location
    Cork, Ireland, Republic of
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal in a Macro (2003)

    Thank you very much Hans for your time and effort - I'll try this out as soon as i can and hopefully will not have any stupid questions!!!! Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •