Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row and copy formulas
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 a 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row and copy formulas
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 -- Regards Frank Kabel Frankfurt, Germany Alan wrote: 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 a 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row and copy formulas
Take a look at David McRitchie's INSRTROW() macro:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm In article , "Alan" wrote: 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 a 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row and copy formulas
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row and copy formulas
try this simple procedure
Sub CopyRow() Rows("2:2").Select Selection.Copy Selection.Insert Shift:=xlDown Application.CutCopyMode = False End Sub -- Patrick Molloy Microsoft Excel MVP --------------------------------- "Alan" wrote in message ... 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 a 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert row and copy formulas | Excel Discussion (Misc queries) | |||
Macro to insert a row and copy down formulas from row above | Excel Discussion (Misc queries) | |||
Copy and paste versus copy and insert copied cells | New Users to Excel | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Copy/Insert rows with formulas | Excel Worksheet Functions |