| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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:
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! |
|
#2
|
|||
|
|||
This has been resolved. Here's the new code:
Code:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
With Target
.Offset(1).EntireRow.Insert
.EntireRow.Copy .Offset(1).EntireRow(1)
With .Offset(1).EntireRow
.Cells(1).ClearContents
On Error Resume Next
.SpecialCells(2).ClearContents
On Error GoTo 0
End With
End With
End Sub
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I sort contents of one column based on the contents ofanother column? | Excel Programming | |||
| Excel Macro to Insert the contents of one column to other | Excel Discussion (Misc queries) | |||
| Copy row contents based on contents | Excel Programming | |||
| Insert contents of various cells in a column into one cell? | Excel Discussion (Misc queries) | |||
| Find a value, then insert a row below that row and copy the contents to the new row | Excel Programming | |||
Linear Mode
