Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Subtotal levels in code (2000 SR-1)

    Hi All! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I'm hoping someone can help me... I've been working on creating a different way of allocating interest inventory for my company... It took a long time but I've finally got the old way to tie in to the new way...
    Yahoo!!! I'm balanced!!! <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

    What I need from you is to know if... I have added 2 or more levels of Subtotals on a sheet... By Currency, then Dept, etc... and another sheet with different levels of Subtotals... Can I, in code, create a new table on a third sheet that uses the different subtotal levels to compare/balance...

    What I mean is... I hit the button I created and code runs that drags in the records I want and then adds the levels of subtotals...
    Now I'd like to go to a third sheet and list the currencies, depts, etc, and bring in subtotal data from the other sheets to compare and show balanced...

    In code can I say something like...
    Find Sheet1's Canadian Currency section, Dept 001's Total and put it here... Next to it put Sheet2's Canadian Currency section Dept 001's Total... etc...

    I'm sorry... I don't think I'm making sense.... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Okay... Let's try this... The code below makes the subtotals after the new data is updated... (It works, but please feel free to edit this if I'm doing something silly...)

    Range("A2").Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, 9, 10), _
    Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    Range("B2").Select
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, 9, 10), _
    Replace:=False, PageBreaks:=False, SummaryBelowData:=True

    Can I now use something like Sheet3, Cell(whatever) = Selection.Subtotal Group1?... or do I have to search for the lines with the words "Dept 001 Total", etc and offset to the right column?

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> in Advance for any ideas!!!

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Subtotal levels in code (2000 SR-1)

    Normally I'm against posting a workbook with the problem at hand, but in this case:

    Could you post a small sample workbook with bogus information that is already set up the way you would like it (that is, the end result and some explanation)?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    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: Using Subtotal levels in code (2000 SR-1)

    If I understand what you are asking (and I admit, I am a little confused):
    I think you can do what you want, by NOT even doing any subtotaling and grouping or a macro.
    You should be able to do it DIRECTLY from your data with a pivot table report.
    Pivot table will extract out a summary of the data, by the various sections (ROW field) or section by section (PAGE field) depending on how you set it up.

    Data-pivot table report gets you the wizard.
    Once you create the privot table, you can right click on it to refresh the table (with new "raw data" added) or to modify the layout.

    If I do NOT understand, I also second Jan's suggestion to provide a simple example with more detailed explanation of what you would like, perhaps an example "output table" that you want to generate from the raw table.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Subtotal levels in code (2000 SR-1)

    Thanks Jan... I'll try put something together for an example...

    The workbook has about 20 sheets with countless named ranges referring to one another, so at the moment I'm not sure where to begin the explanation...
    By the way, I didn't create this mess... I'm just the person responsible for making changes before year end...
    Personally I'd rather take the whole thing, scrap it, and do the work in a normalized database... The problem there is that I don't have the time...

    Anyway... I'll post back when I have something to show you....

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Subtotal levels in code (2000 SR-1)

    <hr>countless named ranges <hr>

    RUN to "The Excel MVP page" listed below and download my Name Manager.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Subtotal levels in code (2000 SR-1)

    Thanks again Jan! I downloaded and installed the Name Manager...
    There are 1808 named ranges in this workbook...

    <img src=/S/drop.gif border=0 alt=drop width=23 height=23> Somebody kill me now!!

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Subtotal levels in code (2000 SR-1)

    <hr>1808 named ranges<hr>
    Wow.

    You might try to get rid of names not in use.

    - Make a backup copy of the book.
    - Then you could try filtering using the Unused names checkbox (go get a cup of coffee after checking it, it'll take a while).
    - Select all the now left over names (first click the Multi button, then drag accross the names list) and press delete.
    - Now check the book for any #Name! errors. Especially check all objects (charts, pivot tables, conditional formatting, etcetera). If any got messed up, reload the backup copy.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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