Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveCell in a Range? | Excel Discussion (Misc queries) | |||
select range next to activecell | Excel Programming | |||
Select Activecell in Range | Excel Programming | |||
Name of range containing ActiveCell? | Excel Programming | |||
Saving the activecell range for later use | Excel Programming |