Subtotals with variable total/sum location

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!
--
Dawn Bjork Buzbee

On the limited information provided, that sounds attractive:

It is actually select case

Select Case sh.name
Case "Sheet1"

Case "Sheet2"

Case Else

end Select

--
Regards,
Tom Ogilvy


"Dawn Bjork Buzbee" wrote:

> 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!
> --
> Dawn Bjork Buzbee

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.
--
Dawn Bjork Buzbee


"Tom Ogilvy" wrote:

> On the limited information provided, that sounds attractive:
>
> It is actually select case
>
> Select Case sh.name
> Case "Sheet1"
>
> Case "Sheet2"
>
> Case Else
>
> end Select
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Dawn Bjork Buzbee" wrote:
>
> > 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!
> > --
> > Dawn Bjork Buzbee

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







"Dawn Bjork Buzbee" wrote:

> 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.
> --
> Dawn Bjork Buzbee
>
>
> "Tom Ogilvy" wrote:
>
> > On the limited information provided, that sounds attractive:
> >
> > It is actually select case
> >
> > Select Case sh.name
> > Case "Sheet1"
> >
> > Case "Sheet2"
> >
> > Case Else
> >
> > end Select
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Dawn Bjork Buzbee" wrote:
> >
> > > 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!
> > > --
> > > Dawn Bjork Buzbee