Let's say that Sheets(1) has the formulas
and Sheets(2) through (23) are the sheets you want to paste to.
(There are a variety of ways to determine the sheets to paste to, this
is just one of many)

Notes: You may want to also turn off calculation during the code.
This code usually works in Excel 2000.

Dim x as Integer
With Application
.ScreenUpdating = False ' <<< this one helps speed things up
.EnableEvents = False ' <<< this one makes sure no events fire
.DisplayAlerts = False
End With
For x = 2 to Activeworkbook.Worksheets.Count
Sheets(1).Rows(9).Copy _
Destination:= Sheets(x).Range(cells(row1,1),cells(row2,1))
Next
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With

Let us know if this works for you...
Also let us know if you need any fine tuning...

--
steveB

Remove "AYN" from email to respond
<samer.kurdi@gmail.com> wrote in message
news:1128967403.220863.196900@g47g2000cwa.googlegroups.com...
>I have a Macro that copies a row of formulas, pastes it for each day of
> data, calculates, and then converts to values. It does this across 22
> clients in 22 different sheets. The formulas copied are array fromulas
> that read from 3 sheets downloaded every day averaging 30,000 lines
> each, and, therefore I convert to values to enhance performance and
> make it possible to work with the data without getting bogged down
> every time there's a recalc.
>
> The problem is that the updating process is extremely slow. I have read
> on this newsgroup that bypassing range selections, copying, and pasting
> significanly enhances performance, but cannot figure out how to do
> this. My code is shown below (Sorry the code isn't very elegant (I
> largely learned vb by recording macros and trying to figure them out
> after).
>
>
> My question is: how can I use VB to copy row 9 (actually columns a to
> cb), paste it (with formatting) into the correct row, add the date
> value in col a, calculate, then paste values, all without using
> .select, .copy, .paste, .pastespecial (I am assuming that this is
> possible and will enhance performance significantly).
>
> Thanks so much!
>
>
> ========================
>
> For b = ptop To pbottom ' b = the row number for each entry, ptop and
> pbottom calculated earlier
>
> Rows("9:9").Copy ' copy the forumulas; always in row 9
> Rows(CStr(b) + ":" + CStr(b)).Select
> ActiveSheet.Paste ' Paste 1 row
>
> ' add the value for the date in question
> zz = q + z 'zz= a value the date being processed, q and z
> calculated earlier in the code
>
> Range("a" + CStr(b)) = zz 'insert the value into Column (A),
> variable row
>
> ' convert to value
> Calculate
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues,
> Operation:=xlNone, SkipBlanks _
> :=False, Transpose:=False
>
> Next b
>