copy previous row down 5 rows
 

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

copy previous row down 5 rows
 

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

"Rick" wrote:

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

copy previous row down 5 rows
 

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

"Rick" wrote:

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

copy previous row down 5 rows
 

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
"Rick" wrote:

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

copy previous row down 5 rows
 

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

"Rick" wrote:

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

copy previous row down 5 rows
 

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

"Rick" wrote:

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

copy previous row down 5 rows
 

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


Gord Dibben MS Excel MVP

On Sat, 10 Nov 2007 18:39:00 -0800, JLGWhiz
wrote:

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

"Rick" wrote:

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

copy previous row down 5 rows
 

Cause I forgot to do it. :(

"Gord Dibben" wrote:

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


Gord Dibben MS Excel MVP

On Sat, 10 Nov 2007 18:39:00 -0800, JLGWhiz
wrote:

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

"Rick" wrote:

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

copy previous row down 5 rows
 

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.

"Rick" wrote:

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

"Rick" wrote:

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