ActiveSheet.Name?

[Andrew Stedman]

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 Pearson]

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 (e-mail address removed)

 

[JMay]

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]

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 (e-mail address removed)

 

[JMay]

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...

 

[Andrew Stedman]

Hi Chip,

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

Andrew