VBA - Most Efficent Copy & Paste?

[Neutralizer]

Hi All,

I'm looking for a way to bypass the complete copy and paste that you see if you use the macro recorder.

This is a useful reference:

http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

Avoid the use of Copy and Paste whenever Possible:

Sub NoCopyAndPaste()

'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
End Sub

However, I have the same problem as the guy in this post:
http://www.ozgrid.com/forum/showthread.php?t=27407&page=1

How do I paste only formats using this method, is it possible?
If not, what is the most efficent way to do this in VBA?

EDIT: Worth noting that this operation is for a very large number of cells, i.e. copying the formats from a range of 1 x 39 cells and applying it to a range of 14,000 x 39 cells.

Cheers

 

[Jon von der Heyden]

Nothing wrong with:

Range(x).Copy
Range(y).PasteSpecial Paste:= xlFormats
Application.CutCopyMode = False

 

[Neutralizer]

Okay, I was told that unless I bypassed the clipboard it would slow the macro down unneccessarily, but presumably that is not an issue? (Excel 2007 and onwards)

Also, I can't use the conventional method as the range of cells that I'm trying to paste in to is to large and I get a Run-Time error 1004: Selection is too large.
Is there anyway around this in VBA?

 

[Jon von der Heyden]

Well if it were just values then the method of referring to .Value range object property would be quicker because it avoids invoking Copy method. However I don't think this sort of micro-optimisation is worth worrying about. Any alternative I can think of is going to be slower anyway.

In terms of size, you could chop up the range in the loop. What is the size limit?

 

[Neutralizer]

It is possible that I could cut up the range. At the moment I'm getting it to complete the paste operation by doing each column individually, however I'm facing the prospect that soon it won't even be able to do that:

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx

It appears that there is a limit of 64,000 unique cells styles, it might be that I'm pushing this limit if it considers the results of conditional formatting to be a unique cell style.

Might have to get off my rear and learn to use Access....