Insert New Rows And Copy Formula Automatically

[nehpets12]

Just a quick question on Excel. I have created a new report which has a number of formulae in it. There will be a requirement by the Depots to insert rows. How do I set it up to the formula in the respective rows automatically is copied into the new cells when the rows are added?

 

[jimboy]

What about a macro?
Change "F" to the row with the formula;

Sub insert_row()

    Selection.EntireRow.Insert
    Range("F" & Selection.Row).FillDown
End Sub

 

[bmacias]

Hi,

As far as I know, this can only be done "automatically" via a macro.

If you want the code, let me know.

Cheers.

Ben

 

[nehpets12]

I have written this code but if there is a value in one of the cells above the line I insert ... then it copies this value as well I am trying to use paste spiecial formulas .... but is still copying the value as well



Sub Insert_Rows()
'
' Macro8 Macro
' Macro recorded 04/08/2003 by Stephen Hoadley
'

'
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(-1, 0).Range("A1").Select
Application.CutCopyMode = False


End Sub

Select any cell in row 4 and run the macro and you will se what I mean

Book1
	A	B	C	D
1		1
2		2
3		3	Steve
4		4
5		5
6		6
Sheet1

 

[jimboy]

This will do pretty much the same if you only want to copy the formula in col. D

Sub Insert_Rows()

Selection.EntireRow.Insert
Range("D" & Selection.Row).Formula = "=R[-1]C+1"
Range("D" & Selection.Row + 1).Formula = "=R[-1]C+1"


End Sub

 

[nehpets12]

There could be formulas in many columns

 

[Legacy 12630]

If Selection.Rows.Count <> 1 Then Exit Sub
If Selection.Areas.Count <> 1 Then Exit Sub
With Selection.EntireRow
.Offset(-1).Copy
.Insert
On Error Resume Next
.Offset(-1).SpecialCells(xlCellTypeConstants, 23).ClearContents
On Error GoTo 0
End With

 

[OpenIntro]

This is pretty much the exact same thing that I am trying to do as well....I posted it about it here: http://www.mrexcel.com/board2/viewtopic.php?t=58048

nephets, your code that you wrote is PRETTY close to what I need done, but I need 4 rows copied, not just the last row....and I am hoping the macro can also automatically find the FIRST EMPTY row and paste starting there.

 

[Cavill]

Hello I have a similar problem.

I am new to the VBA side of excel so basically I am a bit of a newbie. I don't know how to code in VBA yet but am attempting to learn.

I am creating a dynamic spreadsheet to record my stock portfolio.

I want to make a macro that creates a new row below Row 1 or above Row 2. I want the new row to have the same fill colour used in Row 3 ranging between columns A-J.
I also want this new row to include formulas used in cells J3, I3, G3, F3 and D3. Is this possible?

Additionally I want to create a button (I know how to create a button and assign a macro to it already) that is off to the side that is assigned to the macro that creates the new row, however I want the button fixed to its placed position, and doesnt move down as the new rows are created, anyone know how to do this?

 

[chirag07]

hello all

i also have similar problem

i have created a workbook with 11 columns A to k with 20 rows. Each row is having the same formula(like using v-lookup) in respective columns.
previously i was coping the formulas manually. can i get a shortcut like placing a button in sheet which will ask me how many number of rows you want to insert and the inserted rows contains the formula of above row.