+ Reply to Thread
Results 1 to 5 of 5

Copy Paste Special Values Running Slow any Alternatives

  1. #1
    Forum Contributor
    Join Date
    12-10-2008
    Location
    Hong Kong
    MS-Off Ver
    8
    Posts
    164

    Copy Paste Special Values Running Slow any Alternatives

    Dear All,

    I noticed that copy paste special values is slowing down my program.

    Are there any alternatives to my code below hich would speed up the macro?

    Many thanks in advance foe any help, regards John

    Sub RLV()
    '
    ' RLV Macro
    ' Reserve and Liability Valuation
    '
    Application.ScreenUpdating = False

    Range([RLV_CLEAR]).Clear

    Worksheets("RDF").Range("RLV_NOW").Calculate

    Range("RLV_NOW").Copy

    Range(["RLV_START"]).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Application.CutCopyMode = False

    For CurrentLoop = Range("RLV_STARTLOOP") To Range("RLV_ENDLOOP")

    Range("Current_Record_Number") = CurrentLoop

    Calculate

    Range("RLV_COPYRB").Copy

    Range([RLV_DESTINATION]).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Application.CutCopyMode = False

    Next

    Worksheets("RDF").Range("RLV_NOW").Calculate

    Range("RLV_NOW").Copy

    Range("RLV_FINISH").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Application.CutCopyMode = False

    Application.ScreenUpdating = True

    Worksheets("RDF").Calculate

    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,315

    Re: Copy Paste Special Values Running Slow any Alternatives

    Don't know if it'll make much difference but you could get rid of Select/Selection.

    I've also removed the [] around some of the range names.

    Using [] evaluates what's betwen the brackets.

    Which when I think about it might be what you want to do.

    Anyway, here's the code without Select/Selection and [].
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    10-18-2012
    Location
    Telford, England
    MS-Off Ver
    Excel 2010 (2003)
    Posts
    294

    Re: Copy Paste Special Values Running Slow any Alternatives

    Without switching calculation off in the firdst place, your calculates merely make it happen more often. Perhaps tou run anyway with it off (drives me mad doing that). Otherwise you need to start with
    Please Login or Register  to view this content.
    and have a corresponging
    Please Login or Register  to view this content.
    at the end.

  4. #4
    Forum Contributor
    Join Date
    12-10-2008
    Location
    Hong Kong
    MS-Off Ver
    8
    Posts
    164

    Re: Copy Paste Special Values Running Slow any Alternatives

    Thank you.

    The [] need to remain as they need to be evaluated

    Calculate is set to manual allready.

    I think the problem is using select / selection.

    How to use range.value instead of select / selection?

    I think that this is the problem.

    Regards John

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,315
    John

    Why do you need to use []?

    What are you evaluating?

    If you want to use a value = value type method the ranges will need to be the same size.

    PS Did you try the code I posted ?
    If posting code please use code tags, see here.

+ 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