Create a macro

[JOYCE]

I have a spreadsheet that contains several formulas in
each row. I want to be able to insert a new row in this
spreadsheet that will contain these formulas without
having to copy and paste a row from above or below.

The macro I created works correctly only once. Everything
shifts down from the insertion point which loses the cell
reference, plus it only wants to insert at the exact row
when the macro was recorded.

The only experience I've had in macros was a long time ago
(Lotus123 DOS). Please help...I would love to learn more!



Sub Macro1()
'
' Macro1 Macro
' Inserts a new row with formulas
'
' Keyboard Shortcut: Ctrl+i
'
Rows("103:103").Select
Selection.Insert Shift:=xlDown
End Sub

 

[Bob Phillips]

Joyce,

Try this code

ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).EntireRow.Copy
ActiveCell.PasteSpecial Paste:=xlPasteFormulas

--

HTH

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

 

[Debra Dalgleish]

Instead of creating a macro, you could use John Walkenbach's Enhanced
Data Form. It's a free add-in, allows combo boxes, inserts new rows with
formulas, and has unlimited fields.

http://j-walk.com/ss/dataform/index.htm

 

[Joyce]

This works and I also created a button to run it. Thank
you so much.