Optimization question: how to bypass copy and paste

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

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
>

Instead of
.Copy
.PasteValue
use
.Value=.Value

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <1128967403.220863.196900@g47g2000cwa.googlegroups.com>,
samer.kurdi@gmail.com says...
> 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
>
>