Looping Macro for fixed number of rows

[Guest]

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.

 

[Oliver Ferns via OfficeKB.com]

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

 

[JE McGimpsey]

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

 

[Guest]

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

Regards, RobertR

 

[JE McGimpsey]

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

 

[Guest]

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.