# Problem with looping through sheets and creating subtotals

#### rootdown42

##### Board Regular
I have a workbook with multiple sheets that all have the same layout. I want to loop through those sheets and apply the Subtotal function to them. However, I'm struggling even to select the data to apply the Subtotals to. When the code to select the range is executed I get a run-time 1004 error for reasons I don't understand. There is nothing on any of these sheets that would prevent this range from being selected. If I execute the code while the active sheet equals the sheet selected in the array I don't receive the 1004 error, but nothing gets selected.

Code:
``````    Dim iSheet As Worksheet

'Cycle through Summary sheets to add subtotals
For Each iSheet In Worksheets(Array("Qtr Summary", "Mo1 Summary", "Mo2 Summary", "Mo3 Summary", "YTD Summary"))
iSheet.Range("F7", iSheet.Range("F7").End(xlDown).End(xlToRight)).Select <---1004 Error occurs or nothing is selected
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, _
9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Replace:=False, _
PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, _
9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Replace:=False, _
PageBreaks:=False, SummaryBelowData:=True
Next iSheet``````

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You can't select accross sheets like that, try this:

Rich (BB code):
``````  Dim iSheet As Worksheet

'Cycle through Summary sheets to add subtotals
For Each iSheet In Worksheets(Array("Qtr Summary", "Mo1 Summary", "Mo2 Summary", "Mo3 Summary", "YTD Summary"))
with  iSheet.Range("F7", iSheet.Range("F7").End(xlDown).End(xlToRight))
.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, _
9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Replace:=False, _
PageBreaks:=False, SummaryBelowData:=True
.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, _
9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Replace:=False, _
PageBreaks:=False, SummaryBelowData:=True
end with
Next iSheet``````
Hope that helps.

Thanks for the response schielrn. It worked as you said. Just my clarification, so I can't use the .Select method on a different sheet other than the active sheet? Could I have used Sheet1.Activate and then done the selection?

One other issue - after I ran the code both levels of subtotals were created. However, something funky occured. When I stepped through the code the first subtotal applied as expected. However, after the second subtotal was applied it didn't appear to capture all the data within the subtotal. You could tell because the Grand Total for the second subtotal was listed ABOVE the last unique customer. My guess is that after the first subtotal was applied, the range to be used in the second subtotal needed to include the additional rows inserted from the first subtotal, but instead still used the range defined from the xlDown/xlRight command. Does this mean that I need to resize the range before applying the second subtotal?

You may need to use 2 with statements like you said to resize the 2nd one:

Rich (BB code):
``````  Dim iSheet As Worksheet

'Cycle through Summary sheets to add subtotals
For Each iSheet In Worksheets(Array("Qtr Summary", "Mo1 Summary", "Mo2 Summary", "Mo3 Summary", "YTD Summary"))
with  iSheet.Range("F7", iSheet.Range("F7").End(xlDown).End(xlToRight))
.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, _
9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Replace:=False, _
PageBreaks:=False, SummaryBelowData:=True
end with
with  iSheet.Range("F7", iSheet.Range("F7").End(xlDown).End(xlToRight))

.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, _
9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Replace:=False, _
PageBreaks:=False, SummaryBelowData:=True
end with
Next iSheet``````
But not exactly sure if that will work? And yes if you would have activated that sheet or selected it first then your code would work, but you shouldn't really ever use select or activate in code.

Hope thta helps.

Cool. Thanks again for the help.

Replies
1
Views
239
Replies
2
Views
271
Replies
0
Views
211
Replies
2
Views
599
Replies
3
Views
382

1,206,718
Messages
6,074,505
Members
446,072
Latest member
OrangeYellow

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back