I need a macro that will do the following for me:
I have an accounts programme that outputs to Excel. Cells
C1:C70 contain the code "1300"; cells C74:c80 contain the
code "1310" and so on for 820 rows. I need to insert a
row under row 70 and to put the total of J1:J70 in cell
N71. So the macro will travel down Column C and when the
code changes, insert a row, and put the total in Column
N. Thus I will end up with the total of each code in
Column N. I'm sure it's not difficult, but I can't work
it out myself.
Thanks.
Peter

One way:

Public Sub SubtotalIt()
Dim cell As Range
Application.DisplayAlerts = False
Cells.Subtotal GroupBy:=3, _
Function:=xlSum, _
TotalList:=Array(10), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
Application.DisplayAlerts = True
For Each cell In Columns(3).Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
If InStr(cell.Text, " Total") Then _
cell.Offset(0, 7).Cut cell.Offset(0, 11)
Next cell
End Sub


In article ,
"Peter" wrote:

I need a macro that will do the following for me:
I have an accounts programme that outputs to Excel. Cells
C1:C70 contain the code "1300"; cells C74:c80 contain the
code "1310" and so on for 820 rows. I need to insert a
row under row 70 and to put the total of J1:J70 in cell
N71. So the macro will travel down Column C and when the
code changes, insert a row, and put the total in Column
N. Thus I will end up with the total of each code in
Column N. I'm sure it's not difficult, but I can't work
it out myself.
Thanks.
Peter

Very many thanks. I have run this and it works fine. See
my thanks to the others.

Peter
-----Original Message-----
One way:

Public Sub SubtotalIt()
Dim cell As Range
Application.DisplayAlerts = False
Cells.Subtotal GroupBy:=3, _
Function:=xlSum, _
TotalList:=Array(10), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
Application.DisplayAlerts = True
For Each cell In Columns(3).Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
If InStr(cell.Text, " Total") Then _
cell.Offset(0, 7).Cut cell.Offset(0, 11)
Next cell
End Sub


In article ,
"Peter" wrote:

I need a macro that will do the following for me:
I have an accounts programme that outputs to Excel.
Cells
C1:C70 contain the code "1300"; cells C74:c80 contain
the
code "1310" and so on for 820 rows. I need to insert a
row under row 70 and to put the total of J1:J70 in
cell
N71. So the macro will travel down Column C and when
the
code changes, insert a row, and put the total in
Column
N. Thus I will end up with the total of each code in
Column N. I'm sure it's not difficult, but I can't
work
it out myself.
Thanks.
Peter
.