Hi all,

I've got the following macro, which I use in another project, to insert N rows and copy three formulas in F9:H9. The macro I have copies down each formula into cells F10:F400, G10:G400, etc..

In a new project, I want to do a similar thing, except I only want one formula [a max()+1 formula in J2] and copy the cell contents (NOT the formula!) into the J column of my new row.

Anywho... here's the original macros. Only the last line would need changing.

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

CopySideFormulaDown

End Sub

Sub CopySideFormulaDown()

Range("J2").Select.Value
Selection.AutoFill Destination:=Range("XXX"), Type:=xlFillDefault

Thx

You want to copy the value, and not the formula? That's easy enough.

Instead of

Range("J2").Select.Value
Selection.AutoFill Destination:=Range("XXX"), Type:=xlFillDefault

use

Range("XXX").Formula = Range("J2").Value

Not exactly, Razor

I want the value in J2 to drop into column J of the NEW row I just created with the InsertRow macro.

Taking from what you said, I've corrected it below, but that still doesn't tell it to place the contents of J2 into J[New] (or J[New+1], J[New+2], etc. for an answer of >=2 in the InputBoxq question.

Sorry - failed to post revised statements

Selection.AutoFill Destination:=Range("J2").Value, Type:=xlFillDefault

To rephrase - what ^^^ not just fill ALL cells in the new row(s)? Or, would it just copy J?

Fixed it.
Thx for your help, Razor.

Here's the code, FYI

Sub InsertRowCopyJ2intoJCol()
Dim Rng, n As Long, k As Long
Application.ScreenUpdating = False
Rng = 1
If Rng = "" Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(0, 0)).EntireRow.Insert

k = ActiveCell.Offset(0, 0).Row
n = Cells(k, 10).End(xlToLeft).Column
Range(Cells(k, 10), Cells(k + Val(Rng) - 1, 10)).Formula = Range("J2").Value

I took away the option of multiple rows being inserted, and the whole InputBox, but ... That'll do pig, that'll do...
End Sub