excel training uk - Macro Optimization

Hi,

I would like to speed up a lengthy multiple copy paste macro. I have used:
Application.ScreenUpdating = False
Application.ScreenUpdating = True
I have attempted to use various ways to bypass the clipboard as well, but can not find a solution that works. The approximate approaches I have used thus far are at the bottom of this message. I have tried variations on the below and I have been using names. So Sheet2.Range("B1:B200") I have replaced with Range("SectionCopy").

I am copying a row of cells with formulas and pasting the values into the row of cells below.

Please can you help me find a solution.

Thanks,

James

'Instead of:
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").pasteSpecial
Application.CutCopyMode=False'Clear Clipboard
'Use:
'By-passes the Clipboard
Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")
'Or, if only values are needed:
Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value
'Or, if only formulae are needed:
Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula
'See also FormulaArray and FormulaR1C1 etc
'Instead of:
Sheet1.Range("A1:A200").Copy
Sheet1.Range("A1:A200").PasteSpecial xlPasteValues
Application.CutCopyMode=False'Clear Clipboard
'Use:
Sheet1.Range("A1:A200") = Sheet1.Range("A1:A200").Value