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

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
>

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
>

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
>

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
>

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
>

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 <(E-Mail Removed)>
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
>>

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 <(E-Mail Removed)>
> 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
> >>
>
>

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
> >

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

"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
>