Results 1 to 10 of 10
  1. #1
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts

    Grouping and subtotaling data

    There has been several recent posts where the members were looking for a way to copy the data from a data sheet to a new sheet where the data would be grouped and subtotaled. I have written a class module that is generic enough to be placed in any workbook with properties that can be customized to meet the author's needs. The class module can be imported into the VB editor from the attached zipped file or the code inside the attached workbook copy and pasted into a class module and then changing the class name.

    Class1.png

    The following code is pasted into a standard module where the attributes and their values are customized.
    Code:
    Public Sub GroupCompanies()
    '--------------------------------
    'DECLARE AND SET VARIABLES
    Dim Company As ClsGroupData
    Set Company = New ClsGroupData
    '--------------------------------
    'SOURCE SHEET
    Company.SourceName = "Main"         'SHEET NAME THAT CONTAINS SOURCE DATA (STRING)
    Company.SourceStartRow = 2          'FIRST LINE OF DATA NOT INCLUDING HEADER (INTEGER)
    Company.ColIndex = 1                'SOURCE INDEX COLUMN NUMBER USED AS SORT KEY (INTEGER)
    Company.SourceMinCol = 1            'FIRST LEFT COLUMN OF DATA RANGE TO BE MOVED (INTEGER)
    Company.SourceMaxCol = 7            'LAST COLUMN OF DATA RANGE TO BE MOVED (INTEGER)
    Company.CopyHeader = True           'COPY HEADER TO DESTINATION SHEET (BOOLEAN)(DEFAULT=FALSE)
    Company.SourceHeaderRow = 1         'HEADER ROW ON SOURCE SHEET (INTEGER)
    Company.Total = True                'SUBTOTAL GROUPINGS (BOOLEAN)(DEFAULT=FALSE)
    Company.TotalColIndex = 7           'SOURCE COLUMN TO TOTAL (INTEGER)
    '--------------------------------
    'DESTINATION SHEET
    Company.DestinationName = "Group"   'SHEET NAME THAT CONTAINS DESTINATION DATA (STRING)
    Company.DestinationStartRow = 3     'FIRST LINE OF DATA NOT INCLUDING HEADER (INTEGER)
    Company.DestinationStartCol = 2     'LEFT COLUMN OF DATA RANGE TO BE PLACED (INTEGER)
    '--------------------------------
    'PROCEDURES
    Company.GroupData                   'INTITATE CLASS GROUP PROCEDURE
    Company.PrintData                   'PRINTS GROUPED DATA REPORT (OPTIONAL)
    End Sub
    The instance of the class can be changed to anything you want, for example, Employees. Once the code is in place, by adjusting the parameters, the group report can be grouped by any field. Subtotaling can be turned off or on and the column to be totaled is selectable. The range of columns and rows to be copied can be adjusted as well as the destination rows and columns on the destination sheet.

    If anyone finds this beneficial, I will be happy to help you integrate it into your workbook.

    Maud
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Maud,

    Nice Work!

    However you should really get "WITH" IT!ROTFLOL.gif
    Code:
    Public Sub GroupCompanies()
    '--------------------------------
    'DECLARE AND SET VARIABLES
       Dim Company As ClsGroupData
       Set Company = New ClsGroupData
    
       With Company
    '--------------------------------
    'SOURCE SHEET
           .SourceName = "Main"         'SHEET NAME THAT CONTAINS SOURCE DATA (STRING)
           .SourceStartRow = 2          'FIRST LINE OF DATA NOT INCLUDING HEADER (INTEGER)
           .ColIndex = 1                'SOURCE INDEX COLUMN NUMBER USED AS SORT KEY (INTEGER)
           .SourceMinCol = 1            'FIRST LEFT COLUMN OF DATA RANGE TO BE MOVED (INTEGER)
           .SourceMaxCol = 7            'LAST COLUMN OF DATA RANGE TO BE MOVED (INTEGER)
           .CopyHeader = True           'COPY HEADER TO DESTINATION SHEET (BOOLEAN)(DEFAULT=FALSE)
           .SourceHeaderRow = 1         'HEADER ROW ON SOURCE SHEET (INTEGER)
           .Total = True                'SUBTOTAL GROUPINGS (BOOLEAN)(DEFAULT=FALSE)
           .TotalColIndex = 7           'SOURCE COLUMN TO TOTAL (INTEGER)
    '--------------------------------
    'DESTINATION SHEET
           .DestinationName = "Group"   'SHEET NAME THAT CONTAINS DESTINATION DATA (STRING)
           .DestinationStartRow = 3     'FIRST LINE OF DATA NOT INCLUDING HEADER (INTEGER)
           .DestinationStartCol = 2     'LEFT COLUMN OF DATA RANGE TO BE PLACED (INTEGER)
    '--------------------------------
    'PROCEDURES
           .GroupData                   'INTITATE CLASS GROUP PROCEDURE
           .PrintData                   'PRINTS GROUPED DATA REPORT (OPTIONAL)
    
       End With 'Company
       
    End Sub     'GroupCompanies
    Much less to edit. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

    Maudibe (2015-04-10),zeddy (2015-04-11)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Lol! Of course you are correct as usual but I did leave the object qualifier in the statement so members who are not aware of objects would get the gist of how a class works. The other point that I should have made is that the above code could be integrated into an existing macro and doesn't have to be stand alone.

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2015-04-11)

  6. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Maud/RG

    Wow! RG you are really with it! Maud, you have class! Very nice utility.

    As you are both aware of my fondness for Dim statements, I just wanted to say I was very pleased to see you avoided the auto-instancing of your class object i.e. you were not tempted to use

    Dim Company As New ClsGroupData

    The auto-instancing method (putting the New keyword in the Dim statement) causes VBA to excecute additional hidden code. Glad you avoided it.

    zeddy

  7. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2015-04-11)

  8. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Thanks Zeddy,

    Every instance of you puns astounds me Like you, I try to avoid auto-instancing because of its disadvantages. Glad you made that point.

    Maud

  9. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Maud

    In keeping with RG's With, I was going to mention WithEvents.
    The event I'm specifically talking about is RG's imminent 1000th Thanks.
    Not long to go.
    Keep your eyes on it.

    zeddy

  10. #7
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Am I missing something. Wouldn't it be simpler to just sort and use data>subtotals
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  11. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Supershoe,

    Perhaps, if the user wanted the rows sorted but the data would still have to be copied to a new sheet prior. The flexibility in this class is that the parameters afford the user the ability to repeatedly validate the settings, select which data to move and where to place it while maintaining the cell formatting, the option to copy the header, if and how to subtotal it, and initiate 2 class procedures: group and print. All this can be done with just about any formatted collection of data using an easy to understand standard procedure with dot notation while the bulk code is neatly wrapped and tucked away out of site. The parameters could also be tied to a form to provide a GUI for setting up the report.

    ex. Company.SourceMinCol = Settings_form.Textbox5

    Maud

  12. #9
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    While all of that may be appropriate, I still subscribe to the KISS principle and think this should work for most, if not all where the 1st column is the key. Why MOVE the data? Simply select 1st row of data in col 1 and fire this macro
    Code:
    Option Explicit
    Sub SortSubtotalPrintUngroupSAS()
    Dim totalcol As Long
    'Select 1st row of DATA first
    Application.ScreenUpdating = 0
    totalcol = Cells.Find(What:="Total", LookIn:=xlValues, LookAt:= _
         xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
         MatchCase:=False, SearchFormat:=False).Column
    Selection.CurrentRegion.Sort Key1:=Selection, _
    Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom
    Selection.CurrentRegion.Subtotal GroupBy:=1, _
    Function:=xlSum, TotalList:=Array(totalcol)
    '=printPREVIEW chg to printOUT to print
    ActiveWindow.SelectedSheets.PrintPreview
    Selection.RemoveSubtotal
    Application.ScreenUpdating = 1
    End Sub
    Last edited by RetiredGeek; 2015-04-12 at 13:26. Reason: Added Code Tags
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  13. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    ...where the members were looking for a way to copy the data from a data sheet to a new sheet where the data would be grouped and subtotaled.
    That's fine if you want a rigid procedure that is not customizable. I am sure your code has its place but I think you are missing the point of building it as a class while keeping generic for most applications.

Posting Permissions

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