Apply subtotals to multiple sheets

[ifeelalone]

I am trying to loop to apply subtotals through all of my sheets except "Summary" and "Reference_list".
But I am getting an error "Run-time error 1004 : This can't be applied to the selected range. Select a single cell in a range and try again"

Notes:
1. the table starts on A3
2. apply subtotal in Column J and Column M
3. at each change in Column A

I am using this code:

Sub SubTotals() Dim LastRow As Long
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
If wks.Name <> "Summary" And wks.Name <> "Reference_List" Then
With wks
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A3:S" & LastRow).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(10, 13), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
End If
Next

End Sub

Here's a sample of a sheet:
Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[TH]

G​

[/TH]
[TH]

H​

[/TH]
[TH]

I​

[/TH]
[TH]

J​

[/TH]
[TH]

K​

[/TH]
[TH]

L​

[/TH]
[TH]

M​

[/TH]
[TH]

N​

[/TH]
[TH]

O​

[/TH]
[TH]

P​

[/TH]
[TH]

Q​

[/TH]
[TH]

R​

[/TH]
[TH]

S​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]Title of this sheet here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #00B050"]

Deposits ​

[/TD]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #525252"]

Amount Posted ​

[/TD]
[TD="bgcolor: #525252"][/TD]
[TD="bgcolor: #525252"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD]Month_Name[/TD]
[TD="bgcolor: #002060"]

ColumnB​

[/TD]
[TD="bgcolor: #002060"]

ColumnC​

[/TD]
[TD="bgcolor: #002060"]

ColumnD​

[/TD]
[TD="bgcolor: #002060"]

ColumnE​

[/TD]
[TD="bgcolor: #002060"]

ColumnF ​

[/TD]
[TD="bgcolor: #002060"]

ColumnG ​

[/TD]
[TD="bgcolor: #002060"]

ColumnH ​

[/TD]
[TD="bgcolor: #002060"]

ColumnI ​

[/TD]
[TD="bgcolor: #002060"]

ColumnJ ​

[/TD]
[TD="bgcolor: #002060"]

ColumnK ​

[/TD]
[TD="bgcolor: #002060"]

ColumnL ​

[/TD]
[TD="bgcolor: #BDD7EE"]

ColumnM ​

[/TD]
[TD="bgcolor: #FFFFCC"]

ColumnM ​

[/TD]
[TD="bgcolor: #002060"]

ColumnO​

[/TD]
[TD="bgcolor: #002060"]

ColumnP​

[/TD]
[TD="bgcolor: #FFF2CC"]

ColumnQ​

[/TD]
[TD]

ColumnR​

[/TD]
[TD="bgcolor: #FFFFFF"]

ColumnS​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]January[/TD]
[TD]

01/05/16​

[/TD]
[TD]Alvin Empleo[/TD]
[TD][/TD]
[TD]

1335​

[/TD]
[TD][/TD]
[TD]

1,222.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1,222.00 ​

[/TD]
[TD][/TD]
[TD]

1,222.00​

[/TD]
[TD="bgcolor: #BDD7EE"]

1,222.00 ​

[/TD]
[TD="bgcolor: #F5F7B7"][/TD]
[TD]

1/12/2016​

[/TD]
[TD="bgcolor: #F2F2F2"]

315129462​

[/TD]
[TD="bgcolor: #FFF2CC"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD]January[/TD]
[TD]

01/05/16​

[/TD]
[TD]Alvin Empleo[/TD]
[TD][/TD]
[TD]

1335​

[/TD]
[TD][/TD]
[TD]

1,222.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1,222.00 ​

[/TD]
[TD][/TD]
[TD]

1,222.00​

[/TD]
[TD="bgcolor: #BDD7EE"]

1,222.00 ​

[/TD]
[TD="bgcolor: #F5F7B7"][/TD]
[TD]

1/12/2016​

[/TD]
[TD="bgcolor: #F2F2F2"]

315129462​

[/TD]
[TD="bgcolor: #FFF2CC"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6​

[/TD]
[TD]January[/TD]
[TD]

01/05/16​

[/TD]
[TD]Alvin Empleo[/TD]
[TD][/TD]
[TD]

1335​

[/TD]
[TD][/TD]
[TD]

1,222.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1,222.00 ​

[/TD]
[TD][/TD]
[TD]

1,222.00​

[/TD]
[TD="bgcolor: #BDD7EE"]

1,222.00 ​

[/TD]
[TD="bgcolor: #F5F7B7"][/TD]
[TD]

1/12/2016​

[/TD]
[TD="bgcolor: #F2F2F2"]

315129462​

[/TD]
[TD="bgcolor: #FFF2CC"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Alvin Empleo[/TD]
[/TR]
</tbody>[/TABLE]

 

[ifeelalone]

Hope you guys can help me..

 

[Domenic]

Maybe one or more sheets do not have any data? Maybe you could check for existing data before applying the subtotal. For example, you can check whether the last row in Column A is greater than Row 3...

Sub SubTotals()

    Dim LastRow As Long
    Dim wks As Worksheet
    
    For Each wks In ActiveWorkbook.Worksheets
        If wks.Name <> "Summary" And wks.Name <> "Reference_List" Then
            With wks
                LastRow = .Range("A" & [COLOR=#ff0000].[/COLOR]Rows.Count).End(xlUp).Row
                [COLOR=#ff0000]If LastRow > 3 Then[/COLOR]
                    .Range("A3:S" & LastRow).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(10, 13), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
                [COLOR=#ff0000]End If[/COLOR]
            End With
        End If
    Next
    
End Sub

Also, note that I've qualified the reference for Rows.Count by adding a period before it.

Hope this helps!

 

[ifeelalone]

Worked perfectly. Thank you Mr. Domenic!