Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimization question: how to bypass copy and paste
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 wrote in message oups.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Table formula optimization Question | Excel Discussion (Misc queries) | |||
Copy/Paste Question | Excel Discussion (Misc queries) | |||
Copy/Paste question | Excel Worksheet Functions | |||
Optimization question | Excel Discussion (Misc queries) | |||
Copy Paste Question | Excel Discussion (Misc queries) |