Define range with activecell
Hi all
Trying to write a sub to copy a row to a new row directly below
Receiving this error
Run-time error '1004':
PasteSpecial method of Range class failed
Unsure as to why
Seems should work
Also appreciate any commentary to restructure the code if any ideas to
make it beeter more effifcient (example, hard coded column IV (230).
What happend if hidden? What happens when Excel 12 is released? 16K
Columns.
Thanks much
-goss
Sub RowCopy()
'Copy current row to next row
'Preserve formats and formulas
'marc
'April 25, 2006
Dim rngCopy As Range
Dim rngDestination As Range
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
ActiveCell.End(xlToLeft).Select
Set rngCopy = Range(ActiveCell, ActiveCell.Offset(0, 230))
rngCopy.Copy
Application.CutCopyMode = False
ActiveCell.End(xlToLeft).Select
Set rngDestination = Range(ActiveCell.Offset(1, 0), ActiveCell(1, 230))
With rngDestination
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteFormulasAndNumberFormats
End With
'Cleanup
Set rngCopy = Nothing
Set rngDestination = Nothing
With Application
.CutCopyMode = True
.DisplayAlerts = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
|