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