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.

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.

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.

You need to add a reference to the MSForms object library, which
is where the DataObject object is defined. In VBA, go to the
Tools menu and choose References. There, scroll down to
"Microsoft Forms 2.0 Object Library" and put a check in the box
next to it.


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





"JMay" wrote in message
news:anSUa.3258$le.124@lakeread06...
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.

appreciate the help; Got it working!! Don't quiet understand though what
"Forms" has to do with "what seems to be" normal spreadsheet/vba stuff...

"Chip Pearson" wrote in message
...
You need to add a reference to the MSForms object library, which
is where the DataObject object is defined. In VBA, go to the
Tools menu and choose References. There, scroll down to
"Microsoft Forms 2.0 Object Library" and put a check in the box
next to it.


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





"JMay" wrote in message
news:anSUa.3258$le.124@lakeread06...
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.

Hi Chip,

Thanks very much for your help, the code works great!

Andrew


in article , Chip Pearson at
wrote on 27/7/03 9:03 PM:

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