insert rows
 

Hi there,
I'm not that familiar with macros and the information I'm finding is leading
me to believe this is what I need to be using. I currently have 8 different
sections in my worksheet and I am trying to figure out how to insert another
blank row in these sections (before the "total" row) when needed...of course
without interupting the rest of the worksheet. Does anyone have a simple
solution to this? Please be very specific in the actions I need to take to
achieve this.

Hi Rager

i would recommend getting rid of the total rows as long as there is a piece
of information in your workbook to specify which group they belong to, then
when new rows are added all you have to do is to sort by the field that you
want to group the data on and then choose data / subtotals and Excel will
subtotal the data for you automatically

this structure will also allow you to use pivot tables, Data / Form to enter
data into the workbook (as long as you've got less than 30 columns of data),
and data / filter / autofilters ...........

here's an example on using data / subtotals:

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Fred...............50.00
3....B...................Steve..............100.00
4....A...................Anne................20.00

is how the workbook currently appears, now i want subtotals on dept, so i
click on cell A2, choose data / sort - Dept (and while i'm there do a
secondary sort on Person)
then choose Data / Subtotal
at each change in Dept
SUM
Amount
click OK
and i end up with

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00
5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

Hope this helps
Cheers
JulieD


"Rager" wrote in message
...
Hi there,
I'm not that familiar with macros and the information I'm finding is
leading
me to believe this is what I need to be using. I currently have 8
different
sections in my worksheet and I am trying to figure out how to insert
another
blank row in these sections (before the "total" row) when needed...of
course
without interupting the rest of the worksheet. Does anyone have a simple
solution to this? Please be very specific in the actions I need to take
to
achieve this.

Thanks for the recommendation Julie...unforturnately, I have to keep the
totals column and row where they are. I've been reading some other posts and
some people have asked similar questions as I have. Again, there is this
"macro" talk and I am not sure exactly how and exactly what actions I need to
do to set this up. Any other suggestions from anyone??? I need help!

"JulieD" wrote:

Hi Rager

i would recommend getting rid of the total rows as long as there is a piece
of information in your workbook to specify which group they belong to, then
when new rows are added all you have to do is to sort by the field that you
want to group the data on and then choose data / subtotals and Excel will
subtotal the data for you automatically

this structure will also allow you to use pivot tables, Data / Form to enter
data into the workbook (as long as you've got less than 30 columns of data),
and data / filter / autofilters ...........

here's an example on using data / subtotals:

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Fred...............50.00
3....B...................Steve..............100.00
4....A...................Anne................20.00

is how the workbook currently appears, now i want subtotals on dept, so i
click on cell A2, choose data / sort - Dept (and while i'm there do a
secondary sort on Person)
then choose Data / Subtotal
at each change in Dept
SUM
Amount
click OK
and i end up with

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00
5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

Hope this helps
Cheers
JulieD


"Rager" wrote in message
...
Hi there,
I'm not that familiar with macros and the information I'm finding is
leading
me to believe this is what I need to be using. I currently have 8
different
sections in my worksheet and I am trying to figure out how to insert
another
blank row in these sections (before the "total" row) when needed...of
course
without interupting the rest of the worksheet. Does anyone have a simple
solution to this? Please be very specific in the actions I need to take
to
achieve this.

Hi Rager

a macro basically will work for you where you can "automate" the process
without intervention ... which means that the macro has to be able to
determine WHERE to put the new row ... is there something in your workbook
that the code can use to determine this ... and
also we'll need a bit more of an idea of the structure of your worksheet ...
could you (using the same layout as i did in my example) give us an idea of
what you're worksheet's layout is. Please do not, however, attach a
workbook to your reply

Cheers
JulieD

"Rager" wrote in message
...
Thanks for the recommendation Julie...unforturnately, I have to keep the
totals column and row where they are. I've been reading some other posts
and
some people have asked similar questions as I have. Again, there is this
"macro" talk and I am not sure exactly how and exactly what actions I need
to
do to set this up. Any other suggestions from anyone??? I need help!

"JulieD" wrote:

Hi Rager

i would recommend getting rid of the total rows as long as there is a
piece
of information in your workbook to specify which group they belong to,
then
when new rows are added all you have to do is to sort by the field that
you
want to group the data on and then choose data / subtotals and Excel will
subtotal the data for you automatically

this structure will also allow you to use pivot tables, Data / Form to
enter
data into the workbook (as long as you've got less than 30 columns of
data),
and data / filter / autofilters ...........

here's an example on using data / subtotals:

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Fred...............50.00
3....B...................Steve..............100.00
4....A...................Anne................20.00

is how the workbook currently appears, now i want subtotals on dept, so i
click on cell A2, choose data / sort - Dept (and while i'm there do a
secondary sort on Person)
then choose Data / Subtotal
at each change in Dept
SUM
Amount
click OK
and i end up with

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00
5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

Hope this helps
Cheers
JulieD


"Rager" wrote in message
...
Hi there,
I'm not that familiar with macros and the information I'm finding is
leading
me to believe this is what I need to be using. I currently have 8
different
sections in my worksheet and I am trying to figure out how to insert
another
blank row in these sections (before the "total" row) when needed...of
course
without interupting the rest of the worksheet. Does anyone have a
simple
solution to this? Please be very specific in the actions I need to
take
to
achieve this.

Julie,
My layout is very similar to what you had:
..........A..................B.................... ..C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00

5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

I need to be able to add the rows because at any given time, you could have
20 names vs. 5. Again, I have about 8 different sections, like the above
layout, and I have a border around the section and also a sum formula in the
total cell.

I guess what would be ideal is to have some sort of button to add another
row or something above the total row to add rows. ??? If a macro is what I
need...how and what steps do I need to achieve this?

I appreciate your help Julie!

"JulieD" wrote:

Hi Rager

a macro basically will work for you where you can "automate" the process
without intervention ... which means that the macro has to be able to
determine WHERE to put the new row ... is there something in your workbook
that the code can use to determine this ... and
also we'll need a bit more of an idea of the structure of your worksheet ...
could you (using the same layout as i did in my example) give us an idea of
what you're worksheet's layout is. Please do not, however, attach a
workbook to your reply

Cheers
JulieD

"Rager" wrote in message
...
Thanks for the recommendation Julie...unforturnately, I have to keep the
totals column and row where they are. I've been reading some other posts
and
some people have asked similar questions as I have. Again, there is this
"macro" talk and I am not sure exactly how and exactly what actions I need
to
do to set this up. Any other suggestions from anyone??? I need help!

"JulieD" wrote:

Hi Rager

i would recommend getting rid of the total rows as long as there is a
piece
of information in your workbook to specify which group they belong to,
then
when new rows are added all you have to do is to sort by the field that
you
want to group the data on and then choose data / subtotals and Excel will
subtotal the data for you automatically

this structure will also allow you to use pivot tables, Data / Form to
enter
data into the workbook (as long as you've got less than 30 columns of
data),
and data / filter / autofilters ...........

here's an example on using data / subtotals:

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Fred...............50.00
3....B...................Steve..............100.00
4....A...................Anne................20.00

is how the workbook currently appears, now i want subtotals on dept, so i
click on cell A2, choose data / sort - Dept (and while i'm there do a
secondary sort on Person)
then choose Data / Subtotal
at each change in Dept
SUM
Amount
click OK
and i end up with

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00
5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

Hope this helps
Cheers
JulieD


"Rager" wrote in message
...
Hi there,
I'm not that familiar with macros and the information I'm finding is
leading
me to believe this is what I need to be using. I currently have 8
different
sections in my worksheet and I am trying to figure out how to insert
another
blank row in these sections (before the "total" row) when needed...of
course
without interupting the rest of the worksheet. Does anyone have a
simple
solution to this? Please be very specific in the actions I need to
take
to
achieve this.

Hi Rager

the main problem with this sort of macro is say the formula in C4 is
=SUM(C2:C3)
if you add in three rows you'ld want the formula to automatically update to
=SUM(C2:C6)
however this won't happen ... the easiest option is the inserting of a row
(can have row height set to 1 or whatever above the total row) so that in
the example above the first formula would be in C5 and read =SUM(C2:C4), the
new rows would be inserted above row C4 so the formula would automatically
adjust.

Can you worksheet structure be changed to accommodate this?

Cheers
JulieD

"Rager" wrote in message
...
Julie,
My layout is very similar to what you had:
.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00

5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

I need to be able to add the rows because at any given time, you could
have
20 names vs. 5. Again, I have about 8 different sections, like the above
layout, and I have a border around the section and also a sum formula in
the
total cell.

I guess what would be ideal is to have some sort of button to add another
row or something above the total row to add rows. ??? If a macro is what
I
need...how and what steps do I need to achieve this?

I appreciate your help Julie!

"JulieD" wrote:

Hi Rager

a macro basically will work for you where you can "automate" the process
without intervention ... which means that the macro has to be able to
determine WHERE to put the new row ... is there something in your
workbook
that the code can use to determine this ... and
also we'll need a bit more of an idea of the structure of your worksheet
...
could you (using the same layout as i did in my example) give us an idea
of
what you're worksheet's layout is. Please do not, however, attach a
workbook to your reply

Cheers
JulieD

"Rager" wrote in message
...
Thanks for the recommendation Julie...unforturnately, I have to keep
the
totals column and row where they are. I've been reading some other
posts
and
some people have asked similar questions as I have. Again, there is
this
"macro" talk and I am not sure exactly how and exactly what actions I
need
to
do to set this up. Any other suggestions from anyone??? I need help!

"JulieD" wrote:

Hi Rager

i would recommend getting rid of the total rows as long as there is a
piece
of information in your workbook to specify which group they belong to,
then
when new rows are added all you have to do is to sort by the field
that
you
want to group the data on and then choose data / subtotals and Excel
will
subtotal the data for you automatically

this structure will also allow you to use pivot tables, Data / Form to
enter
data into the workbook (as long as you've got less than 30 columns of
data),
and data / filter / autofilters ...........

here's an example on using data / subtotals:

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Fred...............50.00
3....B...................Steve..............100.00
4....A...................Anne................20.00

is how the workbook currently appears, now i want subtotals on dept,
so i
click on cell A2, choose data / sort - Dept (and while i'm there do a
secondary sort on Person)
then choose Data / Subtotal
at each change in Dept
SUM
Amount
click OK
and i end up with

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00
5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

Hope this helps
Cheers
JulieD


"Rager" wrote in message
...
Hi there,
I'm not that familiar with macros and the information I'm finding is
leading
me to believe this is what I need to be using. I currently have 8
different
sections in my worksheet and I am trying to figure out how to insert
another
blank row in these sections (before the "total" row) when
needed...of
course
without interupting the rest of the worksheet. Does anyone have a
simple
solution to this? Please be very specific in the actions I need to
take
to
achieve this.

Rager, search "Insert a blank row(2)" and
"Insert a blank row". If you have the word
"Total" (or any identifiable word for every total line")
the codes provided by RON is very good. I do not know
VBA but manged to put together macro and codes to
achieve what I wanted. I have the same situation as
you ie. totals in different ranges. Record the macro
first using DataSubtotal as JulieD suggested and I
am sure Ron will assist in inserting the Column.

RobertR

Julie,
First, I really appreciate your help. I don't see a problem with what you
are saying...just tell me exactly what I need to do and I will give it a try!
Thank you again for all your replies!

"JulieD" wrote:

Hi Rager

the main problem with this sort of macro is say the formula in C4 is
=SUM(C2:C3)
if you add in three rows you'ld want the formula to automatically update to
=SUM(C2:C6)
however this won't happen ... the easiest option is the inserting of a row
(can have row height set to 1 or whatever above the total row) so that in
the example above the first formula would be in C5 and read =SUM(C2:C4), the
new rows would be inserted above row C4 so the formula would automatically
adjust.

Can you worksheet structure be changed to accommodate this?

Cheers
JulieD

"Rager" wrote in message
...
Julie,
My layout is very similar to what you had:
.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00

5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

I need to be able to add the rows because at any given time, you could
have
20 names vs. 5. Again, I have about 8 different sections, like the above
layout, and I have a border around the section and also a sum formula in
the
total cell.

I guess what would be ideal is to have some sort of button to add another
row or something above the total row to add rows. ??? If a macro is what
I
need...how and what steps do I need to achieve this?

I appreciate your help Julie!

"JulieD" wrote:

Hi Rager

a macro basically will work for you where you can "automate" the process
without intervention ... which means that the macro has to be able to
determine WHERE to put the new row ... is there something in your
workbook
that the code can use to determine this ... and
also we'll need a bit more of an idea of the structure of your worksheet
...
could you (using the same layout as i did in my example) give us an idea
of
what you're worksheet's layout is. Please do not, however, attach a
workbook to your reply

Cheers
JulieD

"Rager" wrote in message
...
Thanks for the recommendation Julie...unforturnately, I have to keep
the
totals column and row where they are. I've been reading some other
posts
and
some people have asked similar questions as I have. Again, there is
this
"macro" talk and I am not sure exactly how and exactly what actions I
need
to
do to set this up. Any other suggestions from anyone??? I need help!

"JulieD" wrote:

Hi Rager

i would recommend getting rid of the total rows as long as there is a
piece
of information in your workbook to specify which group they belong to,
then
when new rows are added all you have to do is to sort by the field
that
you
want to group the data on and then choose data / subtotals and Excel
will
subtotal the data for you automatically

this structure will also allow you to use pivot tables, Data / Form to
enter
data into the workbook (as long as you've got less than 30 columns of
data),
and data / filter / autofilters ...........

here's an example on using data / subtotals:

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Fred...............50.00
3....B...................Steve..............100.00
4....A...................Anne................20.00

is how the workbook currently appears, now i want subtotals on dept,
so i
click on cell A2, choose data / sort - Dept (and while i'm there do a
secondary sort on Person)
then choose Data / Subtotal
at each change in Dept
SUM
Amount
click OK
and i end up with

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00
5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

Hope this helps
Cheers
JulieD


"Rager" wrote in message
...
Hi there,
I'm not that familiar with macros and the information I'm finding is
leading
me to believe this is what I need to be using. I currently have 8
different
sections in my worksheet and I am trying to figure out how to insert
another
blank row in these sections (before the "total" row) when
needed...of
course
without interupting the rest of the worksheet. Does anyone have a
simple
solution to this? Please be very specific in the actions I need to
take
to
achieve this.

Thanks Robert...I posted my situation to Ron.

"Robert" wrote:

Rager, search "Insert a blank row(2)" and
"Insert a blank row". If you have the word
"Total" (or any identifiable word for every total line")
the codes provided by RON is very good. I do not know
VBA but manged to put together macro and codes to
achieve what I wanted. I have the same situation as
you ie. totals in different ranges. Record the macro
first using DataSubtotal as JulieD suggested and I
am sure Ron will assist in inserting the Column.

RobertR

Hi Rager

if you'ld like to email me direct i can send you a sample workbook and then
once we've checked its doing what you want i can walk you through setting it
up in your workbook. My email is julied_ng At hcts dot net dot au.

Cheers
JulieD

"Rager" wrote in message
...
Julie,
First, I really appreciate your help. I don't see a problem with what you
are saying...just tell me exactly what I need to do and I will give it a
try!
Thank you again for all your replies!

"JulieD" wrote:

Hi Rager

the main problem with this sort of macro is say the formula in C4 is
=SUM(C2:C3)
if you add in three rows you'ld want the formula to automatically update
to
=SUM(C2:C6)
however this won't happen ... the easiest option is the inserting of a
row
(can have row height set to 1 or whatever above the total row) so that in
the example above the first formula would be in C5 and read =SUM(C2:C4),
the
new rows would be inserted above row C4 so the formula would
automatically
adjust.

Can you worksheet structure be changed to accommodate this?

Cheers
JulieD

"Rager" wrote in message
...
Julie,
My layout is very similar to what you had:
.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00

5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

I need to be able to add the rows because at any given time, you could
have
20 names vs. 5. Again, I have about 8 different sections, like the
above
layout, and I have a border around the section and also a sum formula
in
the
total cell.

I guess what would be ideal is to have some sort of button to add
another
row or something above the total row to add rows. ??? If a macro is
what
I
need...how and what steps do I need to achieve this?

I appreciate your help Julie!

"JulieD" wrote:

Hi Rager

a macro basically will work for you where you can "automate" the
process
without intervention ... which means that the macro has to be able to
determine WHERE to put the new row ... is there something in your
workbook
that the code can use to determine this ... and
also we'll need a bit more of an idea of the structure of your
worksheet
...
could you (using the same layout as i did in my example) give us an
idea
of
what you're worksheet's layout is. Please do not, however, attach a
workbook to your reply

Cheers
JulieD

"Rager" wrote in message
...
Thanks for the recommendation Julie...unforturnately, I have to keep
the
totals column and row where they are. I've been reading some other
posts
and
some people have asked similar questions as I have. Again, there is
this
"macro" talk and I am not sure exactly how and exactly what actions
I
need
to
do to set this up. Any other suggestions from anyone??? I need
help!

"JulieD" wrote:

Hi Rager

i would recommend getting rid of the total rows as long as there is
a
piece
of information in your workbook to specify which group they belong
to,
then
when new rows are added all you have to do is to sort by the field
that
you
want to group the data on and then choose data / subtotals and
Excel
will
subtotal the data for you automatically

this structure will also allow you to use pivot tables, Data / Form
to
enter
data into the workbook (as long as you've got less than 30 columns
of
data),
and data / filter / autofilters ...........

here's an example on using data / subtotals:

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Fred...............50.00
3....B...................Steve..............100.00
4....A...................Anne................20.00

is how the workbook currently appears, now i want subtotals on
dept,
so i
click on cell A2, choose data / sort - Dept (and while i'm there do
a
secondary sort on Person)
then choose Data / Subtotal
at each change in Dept
SUM
Amount
click OK
and i end up with

.........A..................B..................... .C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00
5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

Hope this helps
Cheers
JulieD


"Rager" wrote in message
...
Hi there,
I'm not that familiar with macros and the information I'm finding
is
leading
me to believe this is what I need to be using. I currently have
8
different
sections in my worksheet and I am trying to figure out how to
insert
another
blank row in these sections (before the "total" row) when
needed...of
course
without interupting the rest of the worksheet. Does anyone have
a
simple
solution to this? Please be very specific in the actions I need
to
take
to
achieve this.