Run-time error '1004: Subtotal method of range

Question

I am trying to run a subtotal on a specific sheet (List) using the following code;

ActiveWorkbook.Sheets("List").Activate

Columns("A:G").Select

Selection.Subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(4, 5, 6)

However keep getting the following error (highlighting the final row of code);

Run-time error '1004:

Subtotal method of Range class failed

Any help would be much appreciated!

Thanks

Answer 1

I don't believe you're working on the worksheet that you think you are. To confirm this, replace

ActiveWorkbook.Activate

with

ActiveWorkbook.Select

They are not equivalent. In order to avoid this in the future, explicity work on your desired sheet like this:

Dim ws as Worksheet
Set ws = ThisWorkbook.Sheets("List")

ws.Columns("A:G").Subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(4, 5, 6)

This question has some good answers indicating what I believe to be your issue. Particularly this one.

Answer 2

I have this error on Delphi, the decision has helped is setting of numerical values ​​for xlSum = -4157 and xlSummaryBelow = 1 (look here https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-subtotal-method-excel)