VBA - Insert row, copy contents of original row except for contents of column A

[Royzer]

Hi. I am using the code below to insert a row by double-clicking a cell
The code then copies formulas (and apparently dates) from the origina
row to the new row. Is there any way for me to adjust this code so th
cell in column A is blank after the insert? If so, I need it to wor
like this for all 30+ pages of the workbook. Here's the code I have i
ThisWorkbook:


Code
-------------------

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'David McRitchie, 2007-09-07 insrtrow.htm on double-click
'-- will copy more often than Extend Formulas and Format (tools option)
Cancel = True
Target.EntireRow.Copy
Cells(Target.Row + 1, 1).EntireRow.Insert
Cells(Target.Row + 1, 1).EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
On Error Resume Next
'-- customize range for what cells constants can be removed --
Intersect(Selection, Range("b:IV")).SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub

 

[GS]

Not sure what the other code in your sample does, but this does what
you asked for on any sheet you double-click on...

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
Cancel = True
With Target
.EntireRow.Copy: Cells(.Row, 1).Offset(1).Insert Shift:=xlDown
End With
With Cells(Target.Row, 1).Offset(1): .Select: .ClearContents: End
With
End Sub

Note that you will now have to just start typing to enter EditMode in
cells, OR use the F2 keyboard shortcut.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Don Guillett]

Hi. I am using the code below to insert a row by double-clicking a cell.
The code then copies formulas (and apparently dates) from the original
row to the new row. Is there any way for me to adjust this code so the
cell in column A is blank after the insert? If so, I need it to work
like this for all 30+ pages of the workbook. Here's the code I have in
ThisWorkbook:

Code:
--------------------

  Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByValTarget As Range, Cancel As Boolean)
  'David McRitchie,  2007-09-07    insrtrow.htm on double-click
  '-- will copy more often than  Extend Formulas and Format (tools option)
  Cancel = True
  Target.EntireRow.Copy
  Cells(Target.Row + 1, 1).EntireRow.Insert
  Cells(Target.Row + 1, 1).EntireRow.Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  On Error Resume Next
  '-- customize range for what cells constants can be removed --
  Intersect(Selection, Range("b:IV")).SpecialCells(xlConstants).ClearContents
  On Error GoTo 0
  End Sub

I think thisis what you want.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
'SAS Copies targetrow and clears columns b>>
Application.ScreenUpdating = False
With Target
Rows(.Row + 1).Insert
Rows(.Row).Copy .Offset(1)
Cells(.Row + 1, 2).Resize(, 255).ClearContents
.Offset(1, 1).Select
End With
Application.ScreenUpdating = True
End Sub

 

[Don Guillett]

I think thisis what you want.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
'SAS Copies targetrow and clears columns b>>
Application.ScreenUpdating = False
With Target
Rows(.Row + 1).Insert
Rows(.Row).Copy .Offset(1)
Cells(.Row + 1, 2).Resize(, 255).ClearContents
 .Offset(1, 1).Select
End With
Application.ScreenUpdating = True
End Sub

Should have been this in the ThisWorkbook module
restricted to a double click ONLY in column A

Private Sub Workbook_SheetBeforeDoubleClick _
(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

If Target.Column <> 1 Then Exit Sub
Application.ScreenUpdating = False
With Target
Rows(.Row + 1).Insert
Rows(.Row).Copy .Offset(1)
Cells(.Row + 1, 2).Resize(, 255).ClearContents
..Offset(1, 1).Select
End With
Application.ScreenUpdating = True
End Sub