Looping Macro for fixed number of rows

I have been sourcing for a solution but to
no avail. I seek assistance for a VBA code
to be activated by macro selection.

A2:AU4993 =4992 rows of 192 records
of 26 rows each.
I want to Copy R:AU of the 26th row of
each record and PasteSpecialValue
to R:AU of the 1st row. I would think
the process should start from the last row
and move upwards ie. R4993:AU4993 Copy
and PastSepecialValue to R4968:AU4968.
Then similar action from R4967:AU4967
to R4942:AU4942 and so until the first
record R27:AU27 to R2:R27.

Thank you.
--
RobertR

Hi,

Try this...it is untested but it should work. If not it will point you in
the right direction...

Option Explicit

Sub LoopEm()
Dim rngLoop as Range, rngLastRec as Range, rngFirstRec as Range, lngInc as
Long

Set rngLoop = thisworkbook.sheets(1).range("R2:AU4993")

For lngInc = rngloop.rows.count to 2 step - 26
Set rngLastRec = rngloop.rows(lngInc)
Set rngFirstRec = rngloop.rows(lngInc - 25)
Let rngFirstRec.value = rngLastRec.value
Next lngInc

Set rngFirstRec = Nothing
Set rngLastRec = Nothing

End Sub

Hth,
Oli

--
Message posted via http://www.officekb.com

One way:

Dim i As Long
For i = 2 To 4992 Step 26
Cells(i, 18).Resize(1, 30).Value = _
Cells(i + 25, 18).Resize(1, 30).Value
Next i

In article <[email protected]>,
Robert <[email protected]> wrote:

> I have been sourcing for a solution but to
> no avail. I seek assistance for a VBA code
> to be activated by macro selection.
>
> A2:AU4993 =4992 rows of 192 records
> of 26 rows each.
> I want to Copy R:AU of the 26th row of
> each record and PasteSpecialValue
> to R:AU of the 1st row. I would think
> the process should start from the last row
> and move upwards ie. R4993:AU4993 Copy
> and PastSepecialValue to R4968:AU4968.
> Then similar action from R4967:AU4967
> to R4942:AU4942 and so until the first
> record R27:AU27 to R2:R27.
>
> Thank you.

Dear McGimpsey
Your "One way" works for me (though slow,not your fault)
Thank you very much.

Regards, RobertR

Hmmm... In my test workbook, it takes about 80 milliseconds to cycle
through the whole record set. If you have a lot of calculations, bracket
your code with this:

Dim oldCalc As Long
With Application
.ScreenUpdating = False
oldCalc = .Calculation
.Calculation = xlManual
End With
'...code here
With Application
.Calculation = oldCalc
.Screenupdating = true
End With


In article <[email protected]>,
Robert <[email protected]> wrote:

> Your "One way" works for me (though slow,not your fault)

Dear McGimpsey

Before seeing your second posting I did what you are suggesting i.e. freeze
calculations. Yes it ran very very very fast indeed. I shall now include
your additional codes. Once again thank you very much. I really appreciate
your asssitance.

RobertR.