Nested Subtotal and Macro
 

Sounds like it is using selection and the selection isn't being expanded on
each iteration.

after each subtotal command put in a line like

selection.CurrentRegion.Select

--
Regards,
Tom Ogilvy

"KJaquish" wrote in message
...
I am having a problem creating a macro to create nested subtotals. I used
the Record Macro feature and when I stop recording, the data looks just as
I
want it. However, when I run the macro, it displays one of the Grand
Totals
near, but not at the bottom of the data, and it doesn't include a subtotal
for the last group of data at all. The Grand Total that does appear at
the
bottom appears correct, but the odd formatting and placement make it
difficult to use. How can I get the macro to return the same results that
I
get if I create the subtotals manually?

Nested Subtotal and Macro
 

Thanks for the suggestion, but that didn't work either.
You are correct that the selection is not being expanded -
it is stopping before it gets quite to the bottom.
Using your suggested command selected the cells just
before the Grand Total, which was an improvement, but
still not getting it where it needs to be. I've tried
naming a range that might expand to include the grand
total, but that doesn't seem to work either. The Grand
Total is put just outside the range.

For now I have split the macro in two and am executing it
in separate steps, but it would be much slicker if I
could find a way to streamline it.
Thanks!

-----Original Message-----
Sounds like it is using selection and the selection
isn't being expanded on
each iteration.

after each subtotal command put in a line like

selection.CurrentRegion.Select

--
Regards,
Tom Ogilvy

"KJaquish" wrote in
message
news:BC838A0B-604A-4DF9-A7B1-
...
I am having a problem creating a macro to create
nested subtotals. I used
the Record Macro feature and when I stop recording,
the data looks just as
I
want it. However, when I run the macro, it displays
one of the Grand
Totals
near, but not at the bottom of the data, and it
doesn't include a subtotal
for the last group of data at all. The Grand Total
that does appear at
the
bottom appears correct, but the odd formatting and
placement make it
difficult to use. How can I get the macro to return
the same results that
I
get if I create the subtotals manually?



.

Nested Subtotal and Macro
 

Then just select a single cell before each execution of the subtotal command
(a cell in the data area). Usually subtotal can find the right area.

Range("A1").Select
selection.subtotal

this assumes you are using
selection.Subtotal rather than specifying a range.

However, unless there is a blank line between grand total and the data, then
currentregion should work.

--
Regards,
Tom Ogilvy


wrote in message
...
Thanks for the suggestion, but that didn't work either.
You are correct that the selection is not being expanded -
it is stopping before it gets quite to the bottom.
Using your suggested command selected the cells just
before the Grand Total, which was an improvement, but
still not getting it where it needs to be. I've tried
naming a range that might expand to include the grand
total, but that doesn't seem to work either. The Grand
Total is put just outside the range.

For now I have split the macro in two and am executing it
in separate steps, but it would be much slicker if I
could find a way to streamline it.
Thanks!

-----Original Message-----
Sounds like it is using selection and the selection
isn't being expanded on
each iteration.

after each subtotal command put in a line like

selection.CurrentRegion.Select

--
Regards,
Tom Ogilvy

"KJaquish" wrote in
message
news:BC838A0B-604A-4DF9-A7B1-
...
I am having a problem creating a macro to create
nested subtotals. I used
the Record Macro feature and when I stop recording,
the data looks just as
I
want it. However, when I run the macro, it displays
one of the Grand
Totals
near, but not at the bottom of the data, and it
doesn't include a subtotal
for the last group of data at all. The Grand Total
that does appear at
the
bottom appears correct, but the odd formatting and
placement make it
difficult to use. How can I get the macro to return
the same results that
I
get if I create the subtotals manually?



.

Nested Subtotal and Macro
 

Oh - that's the problem - there is a blank line between
the data and the Grand Total. I'll get rid of that and
try the CurrentRegion again.

Thanks again for your help!
Karen

-----Original Message-----
Then just select a single cell before each execution of
the subtotal command
(a cell in the data area). Usually subtotal can find
the right area.

Range("A1").Select
selection.subtotal

this assumes you are using
selection.Subtotal rather than specifying a range.

However, unless there is a blank line between grand
total and the data, then
currentregion should work.

--
Regards,
Tom Ogilvy


wrote in message
...
Thanks for the suggestion, but that didn't work either.
You are correct that the selection is not being
expanded -
it is stopping before it gets quite to the bottom.
Using your suggested command selected the cells just
before the Grand Total, which was an improvement, but
still not getting it where it needs to be. I've tried
naming a range that might expand to include the grand
total, but that doesn't seem to work either. The Grand
Total is put just outside the range.

For now I have split the macro in two and am executing
it
in separate steps, but it would be much slicker if I
could find a way to streamline it.
Thanks!

-----Original Message-----
Sounds like it is using selection and the selection
isn't being expanded on
each iteration.

after each subtotal command put in a line like

selection.CurrentRegion.Select

--
Regards,
Tom Ogilvy

"KJaquish" wrote
in
message
news:BC838A0B-604A-4DF9-A7B1-
...
I am having a problem creating a macro to create
nested subtotals. I used
the Record Macro feature and when I stop recording,
the data looks just as
I
want it. However, when I run the macro, it displays
one of the Grand
Totals
near, but not at the bottom of the data, and it
doesn't include a subtotal
for the last group of data at all. The Grand Total
that does appear at
the
bottom appears correct, but the odd formatting and
placement make it
difficult to use. How can I get the macro to return
the same results that
I
get if I create the subtotals manually?



.



.