J
jacqui
I posted the following message yesterday but did not
receive any reply. Please could someone kindly advise on
this subject. I'd really appreciate any help with this
one. Many thanks. My original message is below.
Jacqui
I am using the SubTotal method to create totals in a
datafile using the following code:
.UsedRange.Subtotal groupby:=1, Function:=xlSum,
totallist:=colArray, _
Replace:=True
.UsedRange.Subtotal groupby:=4, Function:=xlSum,
totallist:=colArray, _
Replace:=False
.UsedRange.Subtotal groupby:=5, Function:=xlSum,
totallist:=colArray, _
Replace:=False
ActiveSheet.Outline.ShowLevels 6
I then use .styles to format the totals in various fonts
etc and this works fine. However, I need to introduce
another couple of subtotals to the above yet when I tried
this VBA added the subtotals but showlevel buttons beyond
level 8 were not available. When I tried to select
ShowLevel 8 using VBA it returned the error Subscript Out
of Range. Is there a limit to the number of groupby
sweeps and subtotals you can create? If so is there an
alternative method I could use other than a Pivot Table.
Unfortunately the source file cannot be modified to
include totals so I'm dependent on Excel.
Can anyone kindly fix or recommend a workaround.
NB I'm an XL 97 user.
Many thanks
Jacqui
receive any reply. Please could someone kindly advise on
this subject. I'd really appreciate any help with this
one. Many thanks. My original message is below.
Jacqui
I am using the SubTotal method to create totals in a
datafile using the following code:
.UsedRange.Subtotal groupby:=1, Function:=xlSum,
totallist:=colArray, _
Replace:=True
.UsedRange.Subtotal groupby:=4, Function:=xlSum,
totallist:=colArray, _
Replace:=False
.UsedRange.Subtotal groupby:=5, Function:=xlSum,
totallist:=colArray, _
Replace:=False
ActiveSheet.Outline.ShowLevels 6
I then use .styles to format the totals in various fonts
etc and this works fine. However, I need to introduce
another couple of subtotals to the above yet when I tried
this VBA added the subtotals but showlevel buttons beyond
level 8 were not available. When I tried to select
ShowLevel 8 using VBA it returned the error Subscript Out
of Range. Is there a limit to the number of groupby
sweeps and subtotals you can create? If so is there an
alternative method I could use other than a Pivot Table.
Unfortunately the source file cannot be modified to
include totals so I'm dependent on Excel.
Can anyone kindly fix or recommend a workaround.
NB I'm an XL 97 user.
Many thanks
Jacqui