http://www.cpearson.com/excel/clipboar.htm

I used his solution to make this work flawlessly.
------------------------------
Copying To The Clipboard

To access the Windows Clipboard from VBA, you must go through an
intermediate object of the DataObject type. If your VBA procedure will
be working with the clipboard, declare a NEW DataObject object with the
following statement.

Dim MyDataObj As New DataObject

The SetText method of the DataObject variable is used to store a text
string or numeric value in the variable For example:

MyDataObj.SetText "This Is A Text String" Or
MyDataObj.SetText 123.456

This sets the contents of MyDataObj to a value. To copy the contents
of the variable MyDataObj to the Windows clipboard, use the
PutInClipboard method .

MyDataObj.PutInClipboard


------------

Public Sub PutOnClipboard(Obj As Variant)
Dim MyDataObj As New DataObject
MyDataObj.SetText Format(Obj)
MyDataObj.PutInClipboard
End Sub


Public Function GetOffClipboard() As Variant
Dim MyDataObj As New DataObject
MyDataObj.GetFromClipboard
GetOffClipboard = MyDataObj.GetText()
End Function


Public Sub ClearClipboard()
Dim MyDataObj As New DataObject
MyDataObj.SetText ""
MyDataObj.PutInClipboard
End Sub

----------------------------

Elegant solution


--
Craigm
------------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=525298