Fastest way of copying/ pasting formulas in VBA

[BristolJGM]

I would like to copy/ paste a very large number of formulas (all the formulas in some columns), but Excel is very slow to write them.

Here is the code I am using at the moment:

Columns("A:C").Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

I think I can speed this up by not using "Select" but is there any other way of making it faster? I think even without "Select" it will be very slow as Excel takes a long time to write the data in question, which is the main "drag."

Thank you!

 

[rlv01]

Turn off calculation during the copy, and instead of copying the entire column, limit it to just the portion containing data.

    Application.Calculation = xlCalculationManual
    Application.Intersect(Columns("A:C"), ActiveSheet.UsedRange).Copy
    Range("D1").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.Calculation = xlCalculationAutomatic

 

[BristolJGM]

Thank you! That is a bit faster although it's still pretty slow - I think it's just the amount of data/ complexity of the formulas. Does anyone else have any thoughts to shave a bit of time off?

 

[BristolJGM]

Just to add - I should have added more detail in the first question - I am also pasting the formulas in columns A:C into other colums as well (not just D:F).

So after copying columns A:C I will paste into cell D1, then cell G1, then cell J1 and additional cells after this.

There are a lot of cells/ formulas and what happens is that it gets progressively slower each time I paste. This is with calculation set to manual.

What is really time consuming is writing out the data each time into the worksheet. If there is no way of speeding this up, is there a CPU upgrade that could result in Excel writing the data faster (is this just about having fast single threaded performance?)

Any ideas welcome...