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

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:


Thanks!

This has been resolved. Here's the new code:

Thanks!

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

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

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

On Feb 20, 5:19*pm, Royzer wrote:
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).ClearCont ents
* On Error GoTo 0
* End Sub

--------------------

Thanks!

--
Royzer
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

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

On Feb 21, 7:28*am, Don Guillett wrote:
On Feb 20, 5:19*pm, Royzer wrote:







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).ClearCont ents
* On Error GoTo 0
* End Sub

--------------------

Thanks!

--
Royzer

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