| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
VBA - Insert row, copy contents of original row except for contents of columns A-N
Hi. This code inserts a row and copies formulas (only) to the new row. The problem I have run into is that I didn't know our users were adding and subtracting within cells, which means their numbers are being duplicated on the new row when those new cells are supposed to be blank. I need to find a way to make cells on the new row blank from column A through column N.
Thanks! 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
|
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Insert row, copy contents of original row except forcontents of columns A-N
Change
.Cells(1).ClearContents to .Cells(1).Resize(1,14).ClearContents Tim On Feb 21, 10:10*am, Royzer wrote: Hi. This code inserts a row and copies formulas (only) to the new row. The problem I have run into is that I didn't know our users were adding and subtracting within cells, which means their numbers are being duplicated on the new row when those new cells are supposed to be blank. I need to find a way to make cells on the new row blank from column A through column N. Thanks! 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 -------------------- -- Royzer |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Insert row, copy contents of original row except forcontents of columns A-N
On Feb 21, 12:10*pm, Royzer wrote:
Hi. This code inserts a row and copies formulas (only) to the new row. The problem I have run into is that I didn't know our users were adding and subtracting within cells, which means their numbers are being duplicated on the new row when those new cells are supposed to be blank. I need to find a way to make cells on the new row blank from column A through column N. Thanks! 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 -------------------- -- Royzer Earlier post had a slightly different request. "clear for b" Just change the code I sent earlier from 256 to 13. Put in ThisWorkbook module Fires from col 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(, 14).ClearContents ..Offset(1).Select End With Application.ScreenUpdating = True End Sub |
|
#4
|
|||
|
|||
|
Hi,
try using this example Sheets("Sheet Name").Select Range("cell number/s").Select Selection.ClearContents |
|
#5
|
|||
|
|||
|
Quote:
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).Resize(, 14).ClearContents
On Error Resume Next
.SpecialCells(2).ClearContents
On Error GoTo 0
End With
End With
End Sub
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA - Insert row, copy contents of original row except for contents of column A | Excel Programming | |||
| Insert Row Macro based on contents of two columns | Excel Programming | |||
| Copy Contents of 3 Columns into 1 | Excel Programming | |||
| Find a value, then insert a row below that row and copy the contents to the new row | Excel Programming | |||
| Macro to copy cell contents number of columns | Excel Programming | |||
Linear Mode
