Copy cell with individual formatting

[Arkaad]

I need to copy programmaticly some range from one spreadsheet to another.
Cells are formated (In one cell each word has individual
format -strikethrough, bold ).
I know that I can use Copy Method but this method use clipboard and after it
finish clipboard is empty. Is there a way to copy range without using
clipboard.

 

[Bernie Deitrick]

Arkaad,

Try using the copy command with the target range immediately
following, all on one line:

Range("C3").Copy Range("C4")

HTH,
Bernie

 

[Jim Rech]

The only way I know of to copy a cell with individual characters formatted
without using the clipboard is like this:

Range("Dest").Value(xlRangeValueXMLSpreadsheet) = _
Range("Src").Value(xlRangeValueXMLSpreadsheet)

Unfortunately this method is available only in Excel 2003 and 2003.

The only workaround for earlier versions that comes to mind is to paste the
current clipboard contents to a holding sheet, do the normal Excel
copy/paste, and then copy from the holding sheet. How well this works
depends on what is originally in the clipboard.

 

[Jim Rech]

This does seem to clear the clipboard unfortunately, Bernie.

 

[Bernie Deitrick]

Jim,

This does seem to clear the clipboard unfortunately, Bernie.

I thought that was what he wanted....

Is there a way to copy range without using clipboard.

But after rereading, I guess he meant 'without clearing" clipboard.

Bernie

 

[Tom Ogilvy]

You can always get the text, then match it character for character to the
original for the need attributes.

Sub CopyCharacters()
Dim rng1 As Range
Dim rng2 As Range
Dim i As Long
Set rng1 = Cells(1, 1)
Set rng2 = Range("B9")

rng2.Value = rng1.Value
For i = 1 To Len(rng1.Value)
With rng2.Characters(i, 1).Font
.Name = rng1.Characters(i, 1).Font.Name
.Bold = rng1.Characters(i, 1).Font.Bold
.Italic = rng1.Characters(i, 1).Font.Italic
.Underline = rng1.Characters(i, 1).Font.Underline
.ColorIndex = rng1.Characters(i, 1).Font.ColorIndex
End With
Next

End Sub