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

Define range with activecell
 

try
Sub copyrowabove()
With ActiveCell.EntireRow.Insert
Rows(ActiveCell.Row - 1).Copy Rows(ActiveCell.Row)
End Sub

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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

Define range with activecell
 

Thanks Don -

Can't Inserting rows cause problems with formulas?
I was thinking copy down to next empty row
I see yours works by going to empty row and executing
I was thinking execute from active row

Also, is there a way to copy formulas as well, but not data

Thanks
-goss

Define range with activecell
 

Your post said
Trying to write a sub to copy a row to a new row directly below

This without the "with" does that. From where you have your cursor, fire
this macro to insert a row and copy to that row the row from above the
inserted row.

One trick I have used in the past is to have a dummy hidden row with just
the formulas to copy to the inserted row.
rows(1).copy rows(activecell.row)

Sub copyrowabove()
ActiveCell.EntireRow.Insert
Rows(ActiveCell.Row - 1).Copy Rows(ActiveCell.Row)
End Sub



--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Thanks Don -

Can't Inserting rows cause problems with formulas?
I was thinking copy down to next empty row
I see yours works by going to empty row and executing
I was thinking execute from active row

Also, is there a way to copy formulas as well, but not data

Thanks
-goss