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... |
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 |
All times are GMT +1. The time now is 09:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com