Chip I entered your code (below);
I then enter figures (random numbers) in cells C6:C10, then selected them
(that is highlighted them)
and ran the code: Instantly, I got a Compile error: User-defined type not
defined
with the this line (#4) highlighted.

MyDataObj As New DataObject

What have I failed to do?
TIA,

"Chip Pearson" wrote in message
...
A better solution is

Sub CopySum()
Dim Arr As Variant
Dim N As Long
Dim MyDataObj As New DataObject
Dim str As String
Arr = Split(Selection.Address, ",")
For N = LBound(Arr) To UBound(Arr)
str = str & "," & Selection.Worksheet.Name & "!" & Arr(N)
Next N
str = Mid(str, 2)
MyDataObj.SetText "=sum(" & str & ")"
MyDataObj.PutInClipboard
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Chip Pearson" wrote in message
...
Andrew,

Try the following:

Sub CopySum()
Dim MyDataObj As New DataObject
Dim str As String
str = Selection.Worksheet.Name & "!" & Selection.Address
MyDataObj.SetText "=sum(" & str & ")"
MyDataObj.PutInClipboard
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Andrew Stedman" wrote in message
...
Hi all,

I have the following macro that sums any cells that I select:

Sub CopySum()
Dim MyDataObj As New DataObject
Dim str As String

str = Selection.Address
MyDataObj.SetText "=sum(" & str & ")"
MyDataObj.PutInClipboard
End Sub

The macro works fine but I would like to be able to paste the
result into
another worksheet. At the moment if I say select cells A1 and
B1, the result
would be "=sum($A$1,$B$1)" when I hit paste. When I go to the
next worksheet
and paste, the resulting formula obviously doesn¹t work as the
cell
references do not include the original sheet name.

Ive tried adding ActiveSheet.Name to the macro but I couldn¹t
work out how
to add the sheet name before each cell I selected.

Any help greatly appreciated.

Andrew.