1. ## Subtotals (97/2003)

When using the Subtotals option under the Data menu, you get a line with the word "# Total" in column A after each sorted data (see sheet1).
I would like to have a macro that will replace each "# Total" with the data from the previous row, across a number of columns of my choice. for example: if the parameter is 3, data from columns A to C will be copied to the subtotal row (see sheet2).

Avi

2. ## Re: Subtotals (97/2003)

Does this do what you want?

Steve

<pre>Option Explicit
Sub SubtotalsMacro()

With Columns("B:C")
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=+R[-1]C"
.Copy
.PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False
Range("B65536").End(xlUp).Resize(, 2).ClearContents
End Sub</pre>

3. ## Re: Subtotals (97/2003)

Assuming you want to keep "Grand Total", as in Sheet2, use this procedure:

Sub FillTotals(oCell As Range, lngCols As Long)
Dim rng As Range
Dim lngRow As Long
Set rng = oCell.CurrentRegion
For lngRow = 1 To rng.Rows.Count - 1
If Left(rng.Cells(lngRow, rng.Columns.Count).Formula, 9) = "=SUBTOTAL" Then
rng.Rows(lngRow - 1).Resize(2, lngCols).FillDown
End If
Next lngRow
Set rng = Nothing
End Sub

Call it like this, while Sheet1 is the active sheet:

FillTotals Range("A1"), 3

The range can be any cell in the table with subtotals.

4. ## Re: Subtotals (97/2003)

Thank you both. I will check your suggestions.
Avi

5. ## Re: Subtotals (97/2003)

Hi Steve,
Thank you for your answer. It works ok for columns B and C, but as you can see in sheet2 of my file, I would like also to copy the data in column A to the subtotal row instead of the "# Total" that Excel put there.

Thanks,
Avi

6. ## Re: Subtotals (97/2003)

Hi Hans,
I copied the text of your macro into a module using Visual Basic editor , but when I looked in the Macros list (Alt-F8), it wasn't there. Only when I changed the first line to
Sub FillTotals()
I found the Macro FillTotal in the Macros list. When I tried to run it, it failed.
What am I doing wrong?
Can you explain in more details how and where should I enter the line to call it?

Thanks,
Avi

7. ## Re: Subtotals (97/2003)

As I indicated in my reply, the code I posted is a procedure with two arguments, hence it is not a macro - macros are procedures without arguments. I also indicated how to call the procedure. If you wish, you can create a macro that calls the procedure with the correct arguments:

Public Sub EditSubTotals()
FillTotals Range("A1"), 3
End Sub

The reason I used arguments is that it makes the procedure more general, you can use it for different situations.

8. ## Re: Subtotals (97/2003)

Hi Hans,

I apologize for asking in a second time, but the idea of a macro that runs a procedure is new for me.
Thank you very much. Now it is exactly what I needed.

Avi

9. ## Re: Subtotals (97/2003)

No need to apologize! We all have had to learn (and are still learning) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

10. ## Re: Subtotals (97/2003)

If you are still looking for an answer for my code add this before the End Sub:

<pre> With Columns("A:A")
.Replace What:=" Total", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False
.Replace What:="Grand", Replacement:="Grand Total", _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False
End With</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
•