Subtotals with variable total/sum location

[Guest]

I am editing a macro that was working until the scope was expanded:

1) selects a variable range for sorting and subtotals
2) sorts on 2 key fields (columns are same for all worksheets)
3) creates nested subtotals (the GroupBy columns are consistent); the total
column varies with each worksheet (about 10)

I want to easily set the column position (Array) for the totals. Is Do Case
the best option when there are at least 10 different variations?

Any examples would be very appreciated!

 

[Guest]

Thanks, Tom for the reminder on select case.

Initially, the workbook will contain about 10 worksheets for different
services. I already have a routine that will separate the different
worksheets into their own files.

Here are the lines I am working with for the active worksheet. This code was
created by recording, and so, it's not that clean but it works for subtotals:

Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(21), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=11, Function:=xlSum, TotalList:=Array(21), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True

The variable that changes would be the value for =Array( ), as this column
number can vary depending on the active worksheet. I want to create a simple
input for the user so that they can type in the code for the service they are
working on which will input the variable and process the subtotals.

I know this is basic Excel programming and I've done it before but I don't
have easy access to most of my references, and so, I was looking for some
examples or expert help.

Thanks again.

 

[Guest]

Dim sh as Worksheet, v as Variant
for each sh in thisworkbook.Worksheets
select Case sh.name
Case "Sheet1"
v = Array(21,22)
Case "Sheet2"
v = Array(20)
Case "Sheet3"
v= Array(15,17,23)
Case Else
v= Array(21)
End Select
sh.Select
range("A1").currentRegion.Select
Selection.Subtotal GroupBy:=10, _
Function:=xlSum, _
TotalList:=v, _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
range("A1").currentRegion.Select
Selection.Subtotal GroupBy:=11, _
Function:=xlSum, _
TotalList:=v, _
Replace:=False, _
PageBreaks:=False, _
SummaryBelowData:=True
' more code

Next sh

--
Regards,
Tom Ogilvy