I am making a commission excel sheet and I want to make a macro to insert
cells for the sales people to press so they don't have to select Insert and
Rows from the menus. The macro inserts the row just fine but two things are
happening.
1. The cells on the line are all blank, they are not copying the formulas
from the cell on or above. (actually while typing this i think i have
figured this one out)
but more importantly...
2. The SUM fields are not updating after running my insert macro...

Suggestions...

Tom Cusick
Tech Support
Servant PC Resources, Inc.

Hi
have you enabled automatic calculation?

--
Regards
Frank Kabel
Frankfurt, Germany

"Thomas Cusick" schrieb im
Newsbeitrag ...
I am making a commission excel sheet and I want to make a macro to
insert
cells for the sales people to press so they don't have to select
Insert and
Rows from the menus. The macro inserts the row just fine but two
things are
happening.
1. The cells on the line are all blank, they are not copying the
formulas
from the cell on or above. (actually while typing this i think i
have
figured this one out)
but more importantly...
2. The SUM fields are not updating after running my insert macro...

Suggestions...

Tom Cusick
Tech Support
Servant PC Resources, Inc.

I didn't know I had too Enable Automatic Calculation.

As far as me figuring out the first part I did not. :(

What I thought I would do was make the macro to insert the row and then to
copy the cell to the next cell and so on... the problem with this is that
the macro takes you literally when you click or move to a specific cell.
What I need it to do is to insert the new row above the last TOTALS line and
then copy the formulas into that row. But every time I try this I end up
getting specific cells (like the following example:
Selection.EntireRow.Insert
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
Selection.Copy
Range("G10").Select <-- Basically i need this to float where the row
is inserted
ActiveSheet.Paste
Range("A10").Select

Now if i run this macro it will insert a new row at the end of the table
before the TOTALS line, but it copies the formula in the G column in G10
instead of where the new line is inserted.

Does this make any sense??
-----------------------------------------------------------------------
"Frank Kabel" wrote:

Hi
have you enabled automatic calculation?

--
Regards
Frank Kabel
Frankfurt, Germany

"Thomas Cusick" schrieb im
Newsbeitrag ...
I am making a commission excel sheet and I want to make a macro to
insert
cells for the sales people to press so they don't have to select
Insert and
Rows from the menus. The macro inserts the row just fine but two
things are
happening.
1. The cells on the line are all blank, they are not copying the
formulas
from the cell on or above. (actually while typing this i think i
have
figured this one out)
but more importantly...
2. The SUM fields are not updating after running my insert macro...

Suggestions...

Tom Cusick
Tech Support
Servant PC Resources, Inc.

Tom,

Try this, see if it works as you want for the formula insertion part
of it. As for the SUM not updating... depends on what version of
Excel you're running. What I tend to do to ensure that SUMs
automatically update is include a "short row" before the SUM function.
What I mean by this is: Say you have figures in cells A1 to A5, and
a sum function in A6 (=SUM(A1:A5)). Depending on the Excel version
you're using, inserting a row in row 6 to hold a new item will have
one of two effects: It will either update the sum formula to
"=SUM(A1:A6)" (for newer versions), or leave it as "=SUM(A1:A5)" (for
older versions, which produces an incorrect result).

So I use a short row. A1:A5 hold values. A7 has the sum formula,
"=SUM(A1:A6)". In row 6, I put a line of _ characters (the number of
which depends on the size of the numbers you're dealing with), and set
the row height to 4. This serves two purposes - using _ instead of
border settings means you get a gap in the line between cells, which
(IMHO) looks better, and (more importantly) if you want to add
something to the sum range, you put the cursor in row 6 and insert the
row - the new row gets inserted within the existing sum range, and
therefore gets included into the sum formula regardless of the version
of Excel the end user is on.

Hope this helps,

Clayton.

========================================


Sub InsertRowWithExistingFormula()
Selection.EntireRow.Insert
Selection.EntireRow.FormulaR1C1 = _
Selection.EntireRow.Offset(-1, 0).FormulaR1C1
End Sub

See my page
Insert Row and Maintain Formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm


A11: =SUM($A2:OFFSET(A11,-1,0))
then you don't have to rewrite the SUM when you insert a row immediate
before your total row.

But as you know the formulas you are using will not work. Take a look at
the use of OFFSET on the above page. You will have to rewrite your
formulas, and copy down with the fill handle. Then you are ready for future
insertion/deletion of rows.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Thomas Cusick" wrote in message ...
I didn't know I had too Enable Automatic Calculation.

As far as me figuring out the first part I did not. :(

What I thought I would do was make the macro to insert the row and then to
copy the cell to the next cell and so on... the problem with this is that
the macro takes you literally when you click or move to a specific cell.
What I need it to do is to insert the new row above the last TOTALS line and
then copy the formulas into that row. But every time I try this I end up
getting specific cells (like the following example:
Selection.EntireRow.Insert
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
Selection.Copy
Range("G10").Select <-- Basically i need this to float where the row
is inserted
ActiveSheet.Paste
Range("A10").Select

Now if i run this macro it will insert a new row at the end of the table
before the TOTALS line, but it copies the formula in the G column in G10
instead of where the new line is inserted.

Does this make any sense??
-----------------------------------------------------------------------
"Frank Kabel" wrote:

Hi
have you enabled automatic calculation?

--
Regards
Frank Kabel
Frankfurt, Germany

"Thomas Cusick" schrieb im
Newsbeitrag ...
I am making a commission excel sheet and I want to make a macro to
insert
cells for the sales people to press so they don't have to select
Insert and
Rows from the menus. The macro inserts the row just fine but two
things are
happening.
1. The cells on the line are all blank, they are not copying the
formulas
from the cell on or above. (actually while typing this i think i
have
figured this one out)
but more importantly...
2. The SUM fields are not updating after running my insert macro...

Suggestions...

Tom Cusick
Tech Support
Servant PC Resources, Inc.