Results 1 to 4 of 4

Thread: VBA inserting subtotal(s) rows
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default VBA inserting subtotal(s) rows

    Before I totally kill my day trying to do this, I thought I'd post this considering how quickly perfect solutions are created.

    I have a sheet which is already sorted by column H (starting in row 5).
    [thanks, Fluff]

    I now need to put in a subtotal row for each group of names in H5:Hn and total columns I, J, K.

    So, if H5 on has

    John
    John
    John
    Mary
    Mary
    Mary
    Mary
    Kevin
    Kevin

    I need subtotals for I, J, and K for each of those folks below each of their names

    John
    John
    John
    --- John's subtotals for I, J, and K

    Etc.

    I believe I have to work from the bottom up, but am not sure how to create the breaks and then insert the totals for each person.

    Thanks in advance.
    Last edited by kweaver; Sep 12th, 2019 at 03:06 PM.

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA inserting subtotal(s) rows

    Well, I did a macro recording and it created the subtotals as I needed. But, another issue came up.

    While the subtotals appeared as needed, I then called another macro that was to put borders around the data.
    But, when that was called, all of the subtotals vanished!

    This is the subtotal macro that was recorded:

    Code:
    Sub SubTot()
    '
    ' SubTot Macro
    '
    
    '
        Dim lr As Long
        lr = Sheets("Reformatted").Cells(Rows.Count, "A").End(xlUp).Row
        Range("A4:L" & lr).Select
        Selection.Subtotal GroupBy:=8, Function:=xlSum, TotalList:=Array(9, 10, 11) _
            , Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    End Sub
    Edit: it seems if I run SubTot and then Borders on their own rather than being called by the master macro routine, it's fine.
    Last edited by kweaver; Sep 12th, 2019 at 04:16 PM.

  3. #3
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA inserting subtotal(s) rows

    I thought the problem was resolved when I made sure I didn't have anything interrupting the macros. But, when I copied the sheet with the subtotals to a new workbook to save it, the subtotals vanished.

    In the original workbook where the subtotals are being calculated, they've managed to vanish again!
    Last edited by kweaver; Sep 12th, 2019 at 04:26 PM.

  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA inserting subtotal(s) rows

    I think I'm learning...I think it was a problem because I needed the sheet to be active.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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