ExcelBanter - View Single Post - 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 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, 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

Thanks!