Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Conversion To Relative Cells
I recorded a macro and got the following:
Range("A13").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Range("A14").Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False Selection.ClearContents Range("A14").Select I want to make this a macro the user can run that does the following. Whatever cell the user is on, it copies the row above to the row the user is on. There are lists and data validations, so it copies all that over. But I want all the values to be blank. Is there a better way to do this, using relative paths (instead of A13), and not using copy and paste, and maybe a way to do this without having to do ClearContents, so everything but the text is copied. A newbie at Excel vba... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Conversion To Relative Cells
Option Explicit
Sub testme() Dim myCell As Range Dim RngToCopy As Range Set myCell = ActiveCell If myCell.Row = 1 Then MsgBox "Not on this row, buddy!" Exit Sub End If 'copy the entire row? Set RngToCopy = myCell.Offset(-1, 0).EntireRow myCell.EntireRow.Insert RngToCopy.Copy _ Destination:=RngToCopy.Offset(1, 0).Cells(1) 'clean up constants On Error Resume Next 'just in case there are no constants RngToCopy.Offset(1, 0).Cells.SpecialCells(xlCellTypeConstants).ClearCo ntents On Error GoTo 0 End Sub Derek Hart wrote: I recorded a macro and got the following: Range("A13").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Range("A14").Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False Selection.ClearContents Range("A14").Select I want to make this a macro the user can run that does the following. Whatever cell the user is on, it copies the row above to the row the user is on. There are lists and data validations, so it copies all that over. But I want all the values to be blank. Is there a better way to do this, using relative paths (instead of A13), and not using copy and paste, and maybe a way to do this without having to do ClearContents, so everything but the text is copied. A newbie at Excel vba... -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Relative Cells | Excel Discussion (Misc queries) | |||
How to chnge 35 relative cells to 35 absolute cells at one time. | Excel Worksheet Functions | |||
Relative cells in macro - and pasting a formula too! | Excel Discussion (Misc queries) | |||
Relative cells in macro - and pasting a formula too. | Excel Programming | |||
macro to copy absolute cells, then paste relative | Excel Programming |