Copy, calc and repeat

[JayL]

all-
looking for a way to copy cells B1to I1 down 100 rows and calculate then
repeat - B101 to I101 down 100 rows and calculate until the corresponding A
column cell is blank. Perhaps a message box before moving on to the next
100?

Stated another way - If Col A is populated from A1 to A1570 (just an
example) I need to copy the formulas in B1 thru I1 to B1570 thru I1570 - in
blocks of 100 rows.

any ideas?

FYI the reason for this is the B thru I cells contain functions
referencing numerous other sheets in the workbook. The calculating after
pasting a 1000 rows at a time can take up to 2 minutes on a 2.4 P4 with 512
meg ram. This way I think I can get some results to consider while the
processing moves on to other rows.



TIA

J-

[Trevor Shuttleworth]

Try this:

Sub CopyFormulae()
Dim LastRow As Long
Dim CalcStatus As Long
'Debug.Print Now() & " Start"
Application.ScreenUpdating = False
CalcStatus = Application.Calculation
Application.Calculation = xlCalculationManual
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("B1:I1").Copy
Range("B1:I" & LastRow).PasteSpecial _
Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.Calculation = CalcStatus
Application.ScreenUpdating = True
'Debug.Print Now() & " End"
End Sub

It does it all in one go but it should be virtually instantaneous. At least
it is on my P4. However, my formulae were relatively simple so you could
post back with your formulae if it is slower than you might hope. Tried
with 1570 rows of data.

Regards

Trevor


"JayL" <JayL@nospam.com> wrote in message
news:xNCdnaMa0ut-a6_fRVn-1Q@comcast.com...
> all-
> looking for a way to copy cells B1to I1 down 100 rows and calculate then
> repeat - B101 to I101 down 100 rows and calculate until the corresponding
> A column cell is blank. Perhaps a message box before moving on to the next
> 100?
>
> Stated another way - If Col A is populated from A1 to A1570 (just an
> example) I need to copy the formulas in B1 thru I1 to B1570 thru I1570 -
> in blocks of 100 rows.
>
> any ideas?
>
> FYI the reason for this is the B thru I cells contain functions
> referencing numerous other sheets in the workbook. The calculating after
> pasting a 1000 rows at a time can take up to 2 minutes on a 2.4 P4 with
> 512 meg ram. This way I think I can get some results to consider while
> the processing moves on to other rows.
>
>
>
> TIA
>
> J-
>
>