VBA Speed up copy/paste by not using clipboard HELP

[Pippity]

Hi,

I am trying to speed up a macro i have which does lots of copy paste functions after some calculations have been completed.

I tried this:

[INDENT]OutputRow = 3 + ((Modelpoint - 1) * 126)[/INDENT]
        Sheets("ENGINE").Range("A348:DZ473").Value = Sheets("Output").Cells(OutputRow, 1).Value

Modelpoint being a loop that goes through various rows..

I only want values but it deleted all the formulas in the engine sheet that was was trying to copy.

This was my starting point:

[INDENT] Sheets("ENGINE").Activate[/INDENT]
        OutputRow = 3 + ((Modelpoint - 1) * 3)
        Range("A348:DZ473").Select
        Selection.Copy
        Sheets("Output").Activate
        Sheets("Output").Cells(OutputRow, 5).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks:=False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, skipBlanks:=False, Transpose:=False

My macro takes about 20mins to run as I have 3 different parts from the engine that i paste into output sheets.
I think if i can avoid the clipboard I may be able to improve the speed a lot.

Thanks for your help

 

[Caleeco]

i'm not sure how you can bypass the clipboard... however you can modify your code to speed things up. There is no need to select ranges to copy/paste them. See below:

Sheets("ENGINE").Activate
OutputRow = 3 + ((Modelpoint - 1) * 3)
Range("A348:DZ473").Copy
With Sheets("Output").Cells(OutputRow, 5)
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
End With

Also make sure you have turned off screen updating.

Application.ScreenUpdating = False
'All your code here.
Application.ScreenUpdating = True

Hope that helps a bit
Caleeco