Re: Optimization question: how to bypass copy and paste

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@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
samer.kurdi@xxxxxxxxx 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
> Active***.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
>
>
.

Loading