+ Reply to Thread
Results 1 to 3 of 3

Optimization question: how to bypass copy and paste

  1. #1
    samer.kurdi@gmail.com
    Guest

    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. #2
    STEVE BELL
    Guest

    Re: 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
    <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
    >




  3. #3
    Tushar Mehta
    Guest

    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@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
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1