How to Copy Format without using Clipboard?

[Ceeyee]

I want to copy the Format of a large range to another cell location using VBA.

I don't want to use .Copy and .Pastespecial because it will interupt with my PC front end copy and paste operations.

I found some code from the internet which does what I want but it's extremely slow as it uses loops to run through each cell.


Is there any way to do it without using the clipboard and looping through each cell to = the format? I am using Excel 2010.

Thanks.

 

[Kenneth Hobson]

You could use the Office Clipboard Task Pane to get up to 24 last copies. It is on the Home tab of the ribbon. Click little box to the right of the Clipboard word in 2010. For 2007 see: http://spreadsheets.about.com/od/exceltools/qt/070817clipboard.htm

 

[Jaafar Tribak]

You could use the Office Clipboard Task Pane to get up to 24 last copies. It is on the Home tab of the ribbon. Click little box to the right of the Clipboard word in 2010. For 2007 see: http://spreadsheets.about.com/od/exceltools/qt/070817clipboard.htm

It would be nice if one could retrieve the contents of the Office Clipboard programatically

 

[Jaafar Tribak]

No, that constant removes the clipboard content.

Kenneth,

If it is not much trouble, could you replace the XML_SPREADSHEET Const used in my initial code with each of the clipboard Formats Hex constants in the text file dump you posted and see if any of them works for keeping the clipboard contents (including the cell formats)

In theory, at least one of those contants should work.