Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62

    Unanswered: Macro that will SUM all the groups

    Question
    I have a very large spreadsheet that includes Month End Ship info to various customers.
    Is there are a way to have a macro that will SUM all the groups I have?

    Usually I go to the Data, Subtotal:
    In the Add subtotal window - I check all Months Info
    In the Use Function - I choose - Sum
    And at each change in - i choose every group I have starting from Director - ending with StyleNum.
    It is a long proceess, hope there are a way to have macro, which I never recorded.

    Please help. Thank you.


    Columns are:

    Director
    RegionDescription
    Acct_Exec
    AcctExec
    Sold_To
    Name
    SalesGroup
    ProductGroup
    Fashion/Basic
    FabricGroup
    Style_Number
    Color_Code
    Total Units
    Total Dollars
    Jan'07 U
    Jan'07 $
    .
    .
    .
    Dec'07 U
    Dec'07 $

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. Can you post a sample? Anything you can do, can be done in some way with VBA.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    Please see file attached,
    Thank you for your help

  4. #4
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    Here the attachmnt -
    Thank you
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Here is the intial code I had with my own data, before looking at your example.

    Code:
    Sub Macro1()
        Dim MyData As Range, LastCell As Range
        Dim lngRow As Long
        Dim lngCol As Long
         lngRow = Cells(Rows.Count, 1).End(xlUp).Row
        lngCol = Cells(1, 256).Columns.End(xlToLeft).Column
        Set MyData = Range("A1").Resize(lngRow, lngCol)
        With MyData
            .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 8, 9, _
            10, 13), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        End With
    End Sub
    I will take a look at your sample.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    Thank you for the example - I will try to use it in excel macro.
    What the Array(6, 7, 8, 9, 10, 13), Represent?

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    Here is the code for your worksheet. I made a copy called "Test". You can run the code when that is the active worksheet (just to make sure it gives what you want)

    Code:
    Sub SumData()
        Dim MyData As Range
        Dim lngRow As Long
        Dim lngCol As Long
        lngRow = Cells(Rows.Count, 1).End(xlUp).Row
        lngCol = Cells(1, 256).Columns.End(xlToLeft).Column
        Set MyData = Range("A1").Resize(lngRow, lngCol)
        With MyData
            .Subtotal GroupBy:=1, Function:=xlSum, _
                TotalList:=Array(17, 18, 19, 20, 21, 22, 23, 24, 25, 26, _
                    27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40), _
                    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        End With
    End Sub
    The Array numbers refer to the column numbers.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    If that works, great. But if you are doing this regularly, perhaps summarized data on separate worksheet makes more sense. That can be done with formulas or VBA (using Pivot Table which is more flexible than straight Pivot Tables).
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  9. #9
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    Thank you so much..., it is working perfectly for the Column Director. It subtotals all columns for this Director.
    Is there are a way to have it for the below columns ????

    I do really Appreciate your help .

    Director
    RegionDescription
    AcctExec
    Name
    SalesGroup
    ProductGroup
    Fashion/Basic
    FabricGroup
    Style_Number

  10. #10
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    I'm learnign while working, Never use Pivot table. I need to learn more VBA loginc. new to it.

  11. #11
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, perhaps a re-thinking of the data. Is the data layout set already (i.e. columns for each month, etc.)?

    My thought here is that it would be better to have a column identified as Month, and then one column for Number, and one column for Amount. Then all data weill be vertical. I will provide an example.

    What exactly are you trying to accomplish? It seems that Pivot Tables are much more practical. That can be done either through Excel or VBA.

    Also, this is minimal data, can you provide maybe something 100 rows (with the variations)? In other words, I can't tell if I have helped with no variations int he columns you have.
    Last edited by shades; 10-31-07 at 17:33.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  12. #12
    Join Date
    Oct 2003
    Posts
    1,091
    I added two worksheets: NewData has the arrangement I noted above. I added a dynamic named range, DataB, and it is defined as follows:

    =OFFSET(NewData!$A$1,0,0,COUNTA(NewData!$A:$A),COU NTA(NewData!$1:$1))

    That way you can add any new data and it will automatically adjust.

    Then MyPivot is based on this NewData worksheet.

    This might give you some ideas.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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