Copy totals to another workbook (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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)

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

Steve

Reply | Quote