Insert Row and copy formulas

[Alan]

I'm inserting a row and would like the newly inserted row
to have the same cell formulas (relative ref's) and cell
formats as the originally selected row. Is this possible?

The first row of my worksheet has labels. Row 2 thru n
contain data records. Some of the data record cells have
formulas. I want to insert a new row below the labels
with the same cell formats and formula as the other data
records. My current steps are:

WS.Range("A2").Select
Selection.EntireRow.Insert

A row is inserted below the labels but the new row
inherits the cell formats from the label row rather than
the selected 1st data row and there are no forumlas.

If this is not possible during the insertion, is there a
simple way to copy/paste the cell formats and formulas
from the 1st data record to the newly inserted row? I
could then insert the row, set the row cell values to
NULL, copy/paste the 1st data record format/formulas to
the newly inserted row then initialize whatever cell
values I want.

thanks.

 

[Frank Kabel]

Hi
try the following

Sub foo()
Dim rng As Range
Set rng = WS.Range("A2").EntireRow
rng.Offset(1, 0).Insert
rng.Copy
rng.Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
rng.Offset(1, 0).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
End Sub

 

[JE McGimpsey]

Take a look at David McRitchie's INSRTROW() macro:


http://www.mvps.org/dmcritchie/excel/insrtrow.htm

 

[Patrick Molloy]

try this simple procedure


Sub CopyRow()
Rows("2:2").Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

 

[Alan]

Thanks for the suggestions. I used a combination of the
suggestions, my environment is XP / xl 2003. I included
my code for reference:

Public Sub CreateAssignmentRecord()

Dim AssignmentWS As Worksheet
Dim PrevAssignmentNum As Integer

Dim StartingRow As String

Dim SubjectStreet As String

Dim Rng As Range

'1st Row is labels. Row 2 thru n are data records.
'Insert the new row below the labels.
'Copy the formulas, format and cell contents from row 3
to the newly inserted row.
'Delete the newly inserted row cell values which are not
formulas.

StartingRow = "A2"

Set AssignmentWS = Worksheets("Assignment)
PrevAssignmentNum = AssignmentWS.Range
(StartingRow).Value

Set Rng = AssignmentWS.Range(StartingRow).EntireRow
Rng.Offset(0, 0).Insert
Rng.Copy
Rng.Offset(-1, 0).PasteSpecial
Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Rng.Offset(-1, 0).PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Rng.Offset(-1, 0).SpecialCells
(xlConstants).ClearContents

Application.CutCopyMode = False

AssignmentWS.Range(StartingRow).Value =
PrevAssignmentNum + 1

'The User must assign a Subject Street Address so the
'assignment folder may be created using the street
address.
'SubjectStreet = AssignmentWS.Range("$J$3").Value

Do
'Invoke the editor on the new entry so the user
may fill it out.
Application.Run "dataform2.xla!ShowDataForm"

'Reset the selection to the inserted row in case
the user browsed
'in the editor.
AssignmentWS.Range(StartingRow).Select

SubjectStreet = AssignmentWS.Range("$J$3").Value
If SubjectStreet = "" Then
MsgBox "Error: You must assign the Subject
Street Address for a new assignment." & CR & _
" The Street Address is
used when creating the assignment folder."
End If
Loop Until SubjectStreet <> ""

End If

End Sub