copy previous row down 5 rows

[Guest]

Dim Cnt As Integer
I'm try to insert 5 new rows, then copy the previous row down thru the next
5 rows. I'm have some problem in getting it to work. Need help.
Thanks

Range("A" & SheetEnd).Select
Set CpyCel = EndCel.Offset(-1, 0)
Cnt = 5
Do Until Cnt = 0
Selection.EntireRow.Insert
Cnt = Cnt - 1
Loop
RowStr = "" & CpyCel.Row & ":" & CDec(CpyCel.Row)
Selection.EntireRow.Copy

 

[Guest]

Rick,

If I understand what you are trying to do correctly, try this:

Cells(SheetEnd - 1, 1).EntireRow.Copy
Range(Cells(SheetEnd, 1), Cells(SheetEnd + 4, 1)).Insert Shift:=xlDown
Application.CutCopyMode = False

If the macro copies the wrong row or inserts in the wrong place, play with
the row part of the Cells(.... object.

HTH,
Matthew Pfluger

 

[Guest]

Thanks it works ok, except for I want to copy only formula's, how do I alter
your suggestion.

 

[Guest]

Mathew:
Also the statement
Range(Cells(SheetEnd, 1), Cells(SheetEnd + 4, 1)).Insert Shift:=xlDown
causes the code to jump out of the current module to the sheet number vba code

 

[Guest]

Rick, see if you can work with this:

Sub rsz()
Dim lstRw As Long
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
For i = lstRw To 1 Step -1
Cells(i, 1).EntireRow.Copy
Range(Cells(i + 1, 1), Cells(i + 5, 1)).EntireRow.PasteSpecial
Paste:=xlPasteFormulas
If Cells(i, 1).Row <> 1 Then
Cells(i, 1).Resize(5, 4).EntireRow.Insert
End If
Next
Application.CutCopyMode = False
End Sub

 

[Guest]

Be careful of the word wrap on one of those lines.

 

[Gord Dibben]

Rather than warning OP about word wrap why don't you add a continuation mark in
your original code?


Gord Dibben MS Excel MVP

 

[Guest]

Cause I forgot to do it.

Rather than warning OP about word wrap why don't you add a continuation mark in
your original code?


Gord Dibben MS Excel MVP

 

[Guest]

Sorry Gentlemen:
The Code caused the vba sub routine to jump to the vba code of the
sheet I'm trying to do the insert & copy too.
There is got to be a simple way of cleanning up my original code.
It was done by recording a macro by doing the steps manually.

 

[Guest]

Gentlemen:
After many tries and re-tries, here is the answer to the querry:

Dim Cnt As Integer
On Error GoTo ErrInsRow
Cnt = 5
Do Until Cnt = 0
Range("A" & SheetEnd).Select
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Activate
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).Activate
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Cnt = Cnt - 1
Loop