adding rows

[papa]

I have a sheet with formulas referencing cells in the same
row.

At the bottom of the sheet are totals for the different
columns.

Periodically I need to add more entries to the sheet.
This requires me to insert cells between the last entry
and the totals at the bottom. Then I need to copy the
formulas from one row down to the newly inserted rows.

Can I create a macro that will do that automatically - or
at least when directed by a command button? If so, how
would I do that?

TIA Papa

 

[Frank Kabel]

Hi
have a look at
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

 

[Bob Phillips]

Hi Papa,

Here is code to do the copy.

Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(iLastRow, "A").EntireRow.Insert
Cells(iLastRow - 1, "A").EntireRow.Copy
Cells(iLastRow, "A").PasteSpecial Paste:=xlFormulas

Put it in a macro, and just assign the macro to a button.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[papa]

Bob,
This works great. How can I tweak it so that I also carry
the formatting down as well. By formating I mean borders
and such.
Papa

 

[Guest]

Also,
I have buttons that run macros for sorting my spreadsheet
in different ways. I have had to go in and manually
update the macros manually to change the range that gets
sorted to include the newly inserted rows.

Can this be automated?
TIA
Papa

 

[Bob Phillips]

Hi Papa,

Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(iLastRow, "A").EntireRow.Insert
Cells(iLastRow - 1, "A").EntireRow.Copy
Cells(iLastRow, "A").PasteSpecial Paste:=xlFormulas
Cells(iLastRow, "A").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)