PDA

View Full Version : Solved: Insert row with formatting and formulas



akamax_power
06-03-2008, 11:35 AM
I want to be able to double click a cell that has the value "insert item" and directly above it insert a row and adding the formulas and formatting for all the cells. here's the file with a worksheet.

mikerickson
06-03-2008, 12:01 PM
If you put this in the sheet's code module, it should do what you want

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If LCase(Target.Value) = "insert item" Then
With Target.EntireRow
.Insert
.Copy Destination:=.Offset(-1, 0)
On Error Resume Next
.Offset(-1, 0).SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End With
End If
End Sub

akamax_power
06-03-2008, 05:05 PM
It adds the row above but i want the formatting and formulas from the row above the "Insert Item" row. The code you gave me copies the formatting from the row of the "insert item" row.

Thanks

mikerickson
06-03-2008, 06:48 PM
I think this will do what you want.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If LCase(Target.Value) = "insert item" Then
With Target.EntireRow
.Insert
.Offset(-2, 0).Resize(2, .Columns.Count).FillDown
On Error Resume Next
.Offset(-1, 0).SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End With
Cancel = True
End If
End Sub

akamax_power
06-04-2008, 09:01 AM
Perfect!