Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy totals to another workbook (Excel 2003)

    Hi Loungers

    I have a file which give the list of entries on processing for all clients for the day, what happen is that currently the subtotals will give you the totals of all unique entries.
    However, I need to provide a subtotals under a group name instead of individual name.

    I haved attached a sample about my requirement. Sheet1 show the entries in the file and sheet 2 shows the result that I am seeking for.

    Thank in advance
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  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: Copy totals to another workbook (Excel 2003)

    Data-Subtotals will work "directly" but you will have to create a new column with the "AccntName Group" you define.

    I did this in the new sheet, inserting a new column C with the "group Name" and subtotaling grouping on this column. I made teh column width 0.1 to "hide it", but which will allow the "text" to overhang into the other column, making it "appear" that what you wanted. So it is not exactly what you want but is very close and without any coding for special tricks...

    Steve
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy totals to another workbook (Excel 2003)

    Hi Steve

    This is marvelous! This idea is excellent.
    This is a part of the bigger program that I am currently working on and the program is totally using macro.
    Possiblity to use macro on this?

    I appreciate your effort and help on this

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    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: Copy totals to another workbook (Excel 2003)

    Yes a macro is possible, but I am not sure of the need. If anything you could use a lookup table to get the "grouped name".

    If you want a macro, you would have to be specific about all you wanted it to do. If I created a macro I would have it use the bulit-in subtotatling which can simply be called from the menu...

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy totals to another workbook (Excel 2003)

    Hi Steve

    I do have a macro on sub total, but it is not doing what you have showed me.

    Sub AddTotals()
    Dim lngRow As Long
    lngRow = Range("H65536").End(xlUp).Row
    FixColumn lngRow, "C"
    FixColumn lngRow, "M"
    FixColumn lngRow, "O"
    Range("A1").Subtotal GroupBy:=8, Function:=xlSum, _
    TotalList:=Array(6), Replace:=True
    End Sub

    How do I tweak this to produce your result?

    thanks

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  6. #6
    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: Copy totals to another workbook (Excel 2003)

    You have to enter the groups in Column C
    How about this? (I got rid of the dealings with col H since that did not seem appropriate to the sample since Col H is empty)

    <pre>Option Explicit
    Sub AddTotals()
    Dim x As Long
    Columns("C:C").Insert Shift:=xlToRight
    Range("C1") = "Group"
    For x = 2 To Cells(Cells.Rows.Count, 4).End(xlUp).Row
    If UCase(Left(Cells(x, 4), 2)) = "MS" Then
    Cells(x, 3) = "MS**"
    ElseIf UCase(Left(Cells(x, 4), 5)) = "STATE" Then
    Cells(x, 3) = "STATE ST**"
    Else
    Cells(x, 3) = Cells(x, 4)
    End If
    Next
    Columns("C:C").ColumnWidth = 0.1

    Range("A1").Subtotal _
    GroupBy:=3, _
    Function:=xlSum, _
    TotalList:=Array(7), _
    Replace:=True
    End Sub</pre>


    Steve

Posting Permissions

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