adding rows

P

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
 
B

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)
 
P

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
 
G

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
 
B

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)
 
Top