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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"papa" schrieb im Newsbeitrag
...
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

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" wrote in message
...
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

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

-----Original Message-----
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" wrote in message
...
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


.

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)

"papa" wrote in message
...
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

-----Original Message-----
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" wrote in message
...
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


.

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


-----Original Message-----
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" wrote in message
...
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


.