Adding Rows and copying cells

[Jeff S.]

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.

 

[Max]

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

 

[Ron@Buy]

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