Problem with looping through sheets and creating subtotals

[rootdown42]

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.

    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

Can someone please help me understand what is incorrect about the way I'm selecting this range?

 

[schielrn]

You can't select accross sheets like that, try this:

  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.

 

[rootdown42]

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?

Thanks again for your help.

 

[schielrn]

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

  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.

 

[rootdown42]

Cool. Thanks again for the help.