I'm a new Excel user. I'm trying to insert a row and copy a cell to the cell
directly below it. This happens at various locations through the sheet so I
need it to Paste directly below labeled cell.

CALC is the labeled Column and INROWB1 is the labeled Row

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("CALC INROWB1").Select
Selection.Copy
Range("CALC INROWB1+1").Select
ActiveSheet.Paste

There has obvously got to be an better way than this... any ideas.
thanks.

There has obvously got to be an better way than this ... any ideas

Just offering you some lateral thoughts here. Think it's always cleaner to
design/re-design it simple - where you don't have to keep on inserting rows
in-between data lines and then having to do what-not-thereafter. I'd just
keep new data entry in successive lines down, w/o intervening blank lines,
with all row level calculations placed to the adjacent right of the data
area. And do up the summaries, etc on the data cols / calculation cols in
other sheets. Clean and simple, that's key.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik
---
"Jeff S." wrote:
I'm a new Excel user. I'm trying to insert a row and copy a cell to the cell
directly below it. This happens at various locations through the sheet so I
need it to Paste directly below labeled cell.

CALC is the labeled Column and INROWB1 is the labeled Row

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("CALC INROWB1").Select
Selection.Copy
Range("CALC INROWB1+1").Select
ActiveSheet.Paste

There has obvously got to be an better way than this... any ideas.
thanks.

Jeff, I think Max is obviously right but I expect that wasn't the answer you
really wanted to read, so I offer a little macro that I use to do what you
describe:

' InsertRow Macro

Dim Response
Dim RowNo As Integer

RowNo = ActiveCell.Row
Response = MsgBox("Do you want to insert a row above the current cursor
position of row " _
& RowNo & " ? ", vbYesNo)
Select Case Response

Case vbYes

ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert shift:=xlDown
ActiveCell.Offset(-1, 0).Range("A1:Z1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:Z1").Select
ActiveSheet.Paste

Case vnno
'No action

End Select

End Sub

"Max" wrote:

There has obvously got to be an better way than this ... any ideas

Just offering you some lateral thoughts here. Think it's always cleaner to
design/re-design it simple - where you don't have to keep on inserting rows
in-between data lines and then having to do what-not-thereafter. I'd just
keep new data entry in successive lines down, w/o intervening blank lines,
with all row level calculations placed to the adjacent right of the data
area. And do up the summaries, etc on the data cols / calculation cols in
other sheets. Clean and simple, that's key.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik
---
"Jeff S." wrote:
I'm a new Excel user. I'm trying to insert a row and copy a cell to the cell
directly below it. This happens at various locations through the sheet so I
need it to Paste directly below labeled cell.

CALC is the labeled Column and INROWB1 is the labeled Row

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("CALC INROWB1").Select
Selection.Copy
Range("CALC INROWB1+1").Select
ActiveSheet.Paste

There has obvously got to be an better way than this... any ideas.
thanks.