I have a workbook containing several worksheets, I wish to perform a subtotal
on each worksheet. Each worksheet is setup identical in terms of the number
of columns and colum titles etc but differ in the number of rows containing
data.

I am using the following code:

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next

I get an error message saying that saying:

"Subtotal method Of Range class failed"

Can anyone advise?
--
K Hogwood-Thompson

You ned to include the worksheet in the ranges

For Each ws In ActiveWorkbook.Worksheets
ws.Range("A1").Select
ws.Range(Selection, Selection.End(xlToRight)).Select
ws.Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next

or

For Each ws In ActiveWorkbook.Worksheets
set Lastcol = .Range("A1).end(xltoRight)
set LastCell = LastCol.end(xldown)
Set SubtotalRange = .Range(.Range("A1"),LastCell)
SubtotalRange.Subtotal GroupBy:=10, Function:=xlSum,
TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next


"KHogwood-Thompson" wrote:

I have a workbook containing several worksheets, I wish to perform a subtotal
on each worksheet. Each worksheet is setup identical in terms of the number
of columns and colum titles etc but differ in the number of rows containing
data.

I am using the following code:

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next

I get an error message saying that saying:

"Subtotal method Of Range class failed"

Can anyone advise?
--
K Hogwood-Thompson

Thanks for your post Joel, however I have tried both methods that you posted
and both of them result in the same error:

"Subtotal method Of Range class failed"

--
K Hogwood-Thompson


"Joel" wrote:

You ned to include the worksheet in the ranges

For Each ws In ActiveWorkbook.Worksheets
ws.Range("A1").Select
ws.Range(Selection, Selection.End(xlToRight)).Select
ws.Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next

or

For Each ws In ActiveWorkbook.Worksheets
set Lastcol = .Range("A1).end(xltoRight)
set LastCell = LastCol.end(xldown)
Set SubtotalRange = .Range(.Range("A1"),LastCell)
SubtotalRange.Subtotal GroupBy:=10, Function:=xlSum,
TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next


"KHogwood-Thompson" wrote:

I have a workbook containing several worksheets, I wish to perform a subtotal
on each worksheet. Each worksheet is setup identical in terms of the number
of columns and colum titles etc but differ in the number of rows containing
data.

I am using the following code:

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next

I get an error message saying that saying:

"Subtotal method Of Range class failed"

Can anyone advise?
--
K Hogwood-Thompson

Try This idea to put the sum below the last row

Sub SubTotalEachSht()
For Each ws In Worksheets
With ws
lc = .Cells(1, 1).End(xlToRight).Column
lr = .Cells(Rows.Count, lc).End(xlUp).Row
.Cells(lr + 1, lc).Value = _
Application.Sum(Range(.Cells(2, lc), .Cells(lr, lc)))
End With
Next ws
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"KHogwood-Thompson" wrote in
message ...
I have a workbook containing several worksheets, I wish to perform a
subtotal
on each worksheet. Each worksheet is setup identical in terms of the
number
of columns and colum titles etc but differ in the number of rows
containing
data.

I am using the following code:

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next

I get an error message saying that saying:

"Subtotal method Of Range class failed"

Can anyone advise?
--
K Hogwood-Thompson

Don,

Yes this works, but does not perform the subtotal as required, on each sheet
I have transactions that need to be grouped and subtotalled by a column
called "HEAD". Your method does sum a column but is not the correct column
and only performs a grand total.
--
K Hogwood-Thompson


"Don Guillett" wrote:

Try This idea to put the sum below the last row

Sub SubTotalEachSht()
For Each ws In Worksheets
With ws
lc = .Cells(1, 1).End(xlToRight).Column
lr = .Cells(Rows.Count, lc).End(xlUp).Row
.Cells(lr + 1, lc).Value = _
Application.Sum(Range(.Cells(2, lc), .Cells(lr, lc)))
End With
Next ws
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"KHogwood-Thompson" wrote in
message ...
I have a workbook containing several worksheets, I wish to perform a
subtotal
on each worksheet. Each worksheet is setup identical in terms of the
number
of columns and colum titles etc but differ in the number of rows
containing
data.

I am using the following code:

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next

I get an error message saying that saying:

"Subtotal method Of Range class failed"

Can anyone advise?
--
K Hogwood-Thompson

Based on Joel's catch, I think it did what your OP asked for. If desired,
send to my address below, your workbook
along with clear explanation of what you want and before/after example.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"KHogwood-Thompson" wrote in
message ...
Don,

Yes this works, but does not perform the subtotal as required, on each
sheet
I have transactions that need to be grouped and subtotalled by a column
called "HEAD". Your method does sum a column but is not the correct column
and only performs a grand total.
--
K Hogwood-Thompson


"Don Guillett" wrote:

Try This idea to put the sum below the last row

Sub SubTotalEachSht()
For Each ws In Worksheets
With ws
lc = .Cells(1, 1).End(xlToRight).Column
lr = .Cells(Rows.Count, lc).End(xlUp).Row
.Cells(lr + 1, lc).Value = _
Application.Sum(Range(.Cells(2, lc), .Cells(lr, lc)))
End With
Next ws
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"KHogwood-Thompson" wrote in
message ...
I have a workbook containing several worksheets, I wish to perform a
subtotal
on each worksheet. Each worksheet is setup identical in terms of the
number
of columns and colum titles etc but differ in the number of rows
containing
data.

I am using the following code:

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(13),
_
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next

I get an error message saying that saying:

"Subtotal method Of Range class failed"

Can anyone advise?
--
K Hogwood-Thompson

Try Don's code again but make one minor change
from
Application.Sum(Range(.Cells(2, lc), .Cells(lr, lc)))
to
Application.Sum(.Range(.Cells(2, lc), .Cells(lr, lc)))


"Don Guillett" wrote:

Try This idea to put the sum below the last row

Sub SubTotalEachSht()
For Each ws In Worksheets
With ws
lc = .Cells(1, 1).End(xlToRight).Column
lr = .Cells(Rows.Count, lc).End(xlUp).Row
.Cells(lr + 1, lc).Value = _
Application.Sum(Range(.Cells(2, lc), .Cells(lr, lc)))
End With
Next ws
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"KHogwood-Thompson" wrote in
message ...
I have a workbook containing several worksheets, I wish to perform a
subtotal
on each worksheet. Each worksheet is setup identical in terms of the
number
of columns and colum titles etc but differ in the number of rows
containing
data.

I am using the following code:

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next

I get an error message saying that saying:

"Subtotal method Of Range class failed"

Can anyone advise?
--
K Hogwood-Thompson

Hi Joel,

Code does the same as Don's.
--
K Hogwood-Thompson


"Joel" wrote:

Try Don's code again but make one minor change
from
Application.Sum(Range(.Cells(2, lc), .Cells(lr, lc)))
to
Application.Sum(.Range(.Cells(2, lc), .Cells(lr, lc)))


"Don Guillett" wrote:

Try This idea to put the sum below the last row

Sub SubTotalEachSht()
For Each ws In Worksheets
With ws
lc = .Cells(1, 1).End(xlToRight).Column
lr = .Cells(Rows.Count, lc).End(xlUp).Row
.Cells(lr + 1, lc).Value = _
Application.Sum(Range(.Cells(2, lc), .Cells(lr, lc)))
End With
Next ws
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"KHogwood-Thompson" wrote in
message ...
I have a workbook containing several worksheets, I wish to perform a
subtotal
on each worksheet. Each worksheet is setup identical in terms of the
number
of columns and colum titles etc but differ in the number of rows
containing
data.

I am using the following code:

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next

I get an error message saying that saying:

"Subtotal method Of Range class failed"

Can anyone advise?
--
K Hogwood-Thompson

Do you have Excell 2003 or Excel 2007. If 2003 make this change

from
Application.Sum(.Range(.Cells(2, lc), .Cells(lr, lc)))

to
worksheetfunction.Sum(.Range(.Cells(2, lc), .Cells(lr, lc)))


"KHogwood-Thompson" wrote:

Hi Joel,

Code does the same as Don's.
--
K Hogwood-Thompson


"Joel" wrote:

Try Don's code again but make one minor change
from
Application.Sum(Range(.Cells(2, lc), .Cells(lr, lc)))
to
Application.Sum(.Range(.Cells(2, lc), .Cells(lr, lc)))


"Don Guillett" wrote:

Try This idea to put the sum below the last row

Sub SubTotalEachSht()
For Each ws In Worksheets
With ws
lc = .Cells(1, 1).End(xlToRight).Column
lr = .Cells(Rows.Count, lc).End(xlUp).Row
.Cells(lr + 1, lc).Value = _
Application.Sum(Range(.Cells(2, lc), .Cells(lr, lc)))
End With
Next ws
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"KHogwood-Thompson" wrote in
message ...
I have a workbook containing several worksheets, I wish to perform a
subtotal
on each worksheet. Each worksheet is setup identical in terms of the
number
of columns and colum titles etc but differ in the number of rows
containing
data.

I am using the following code:

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next

I get an error message saying that saying:

"Subtotal method Of Range class failed"

Can anyone advise?
--
K Hogwood-Thompson

Neither of those, I am using Excel 2000
--
K Hogwood-Thompson


"Joel" wrote:

Do you have Excell 2003 or Excel 2007. If 2003 make this change

from
Application.Sum(.Range(.Cells(2, lc), .Cells(lr, lc)))

to
worksheetfunction.Sum(.Range(.Cells(2, lc), .Cells(lr, lc)))


"KHogwood-Thompson" wrote:

Hi Joel,

Code does the same as Don's.
--
K Hogwood-Thompson


"Joel" wrote:

Try Don's code again but make one minor change
from
Application.Sum(Range(.Cells(2, lc), .Cells(lr, lc)))
to
Application.Sum(.Range(.Cells(2, lc), .Cells(lr, lc)))


"Don Guillett" wrote:

Try This idea to put the sum below the last row

Sub SubTotalEachSht()
For Each ws In Worksheets
With ws
lc = .Cells(1, 1).End(xlToRight).Column
lr = .Cells(Rows.Count, lc).End(xlUp).Row
.Cells(lr + 1, lc).Value = _
Application.Sum(Range(.Cells(2, lc), .Cells(lr, lc)))
End With
Next ws
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"KHogwood-Thompson" wrote in
message ...
I have a workbook containing several worksheets, I wish to perform a
subtotal
on each worksheet. Each worksheet is setup identical in terms of the
number
of columns and colum titles etc but differ in the number of rows
containing
data.

I am using the following code:

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(13), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next

I get an error message saying that saying:

"Subtotal method Of Range class failed"

Can anyone advise?
--
K Hogwood-Thompson