Macro to insert new row and copy formulas from the above row.

**manny kang** · 12-08-2009, 07:19 AM

Okay, where to begin.. lol.

Currently I use a macro to insert a new row above the cell which is selected, the macro copies the formatting from the row above but I need it, or a separate macro to also copy formula's from the row above in columns "V, W, X, AG, AH, AI."

I have had a look on the web and cant really find anything (or probably not understand it as my knowledge of excel is competent and knowledge of VB is non existent).

Below is the macro which is currently used to insert a new row and copy the formatting from the row above. Any suggestions or help would be much appreciated as I am really being pushed for a solution.

(Apologies if I shouldn't have posted a new thread but I couldn't find anything that quite matched the exact macro I am after)
----------------------------
Sub InsertRow()
Dim Rng, n As Long, k As Long
Application.ScreenUpdating = False
Rng = InputBox("Enter number of rows required.")
If Rng = "" Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(Val(Rng) - 1, 0)).EntireRow.Insert

k = ActiveCell.Offset(-1, 0).Row
n = Cells(k, 2).End(xlToLeft).Column
Range(Cells(k, 2), Cells(k + Val(Rng), n)).FillDown

End Sub

**rwgrietveld** · 12-08-2009, 07:54 AM

Please read the forum rules and follow the link I provide in my signature.

**rwgrietveld** · 12-08-2009, 09:09 AM

This is what I have on the shelve:

Please Login or Register  to view this content.

See the attached WB for the Userform that calls this function.

Not to offense, but your code will run into error when the user gives a Non numeric.

.... and now get those TAGS fixed in your original post.

**manny kang** · 12-08-2009, 12:25 PM

Thanks for the help, apologies for the tags.. they should be removed now.

I still can't seem to get this to work. I have selected F 11 - insert module and pasted the code you've given me but it just changes the name of the "insert row" macro and gives me the same result of inserting a row with all the formatting of the above row but not the formula's.

Please let me know if I am doing something wrong.

I would be happy to use a separate macro to copy the formulas in the row above if it avoids any issues.

Again your help would be much appreciated.

**jas001** · 11-26-2012, 06:08 PM

I am interested in this as well but slightly different. I want to copy a specific row (rows("500:500")) that has all my formulas and formats but no data yet. So i want the macro to copy that row and then insert it above the current row I have selected when i hit the macro shortcut. I'm new to the macro world so any help is appreciated. Also if you can give me a good reference to teach myself some of the basic VBA, that would be great as well.

**jeffreybrown** · 11-26-2012, 06:10 PM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.