Does anyone know how to take the value of the little window on the bottom
right of excel (ie you highlight a group of numbers and it shows the sum or
avg or whatever you choose.) and copy it to the clipboard?

Cheers!

"Job" wrote in message
...
: Does anyone know how to take the value of the little window on the bottom
: right of excel (ie you highlight a group of numbers and it shows the sum
or
: avg or whatever you choose.) and copy it to the clipboard?
:
: Cheers!

This should do the trick

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject

MyData.SetText WorksheetFunction.Sum(Selection)
MyData.PutInClipboard

End Sub

In order to run since MyData is earlybind, you must go to tools reference in
the VBA editor and select Microsoft Forms 2.0 Object Library. Or you can
change to late bind (i.e. use CreateObject)

Just assign the macro to a button or keyboard shortcut and good to go. Note
you can also change the WorksheetFunction from .Sum to any valid function
(i.e. .Average etc.)

Paul D

Thanks Paul! Exactly what I was looking for...
"PaulD" <nospam wrote in message
...
"Job" wrote in message
...
: Does anyone know how to take the value of the little window on the
bottom
: right of excel (ie you highlight a group of numbers and it shows the sum
or
: avg or whatever you choose.) and copy it to the clipboard?
:
: Cheers!

This should do the trick

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject

MyData.SetText WorksheetFunction.Sum(Selection)
MyData.PutInClipboard

End Sub

In order to run since MyData is earlybind, you must go to tools reference
in
the VBA editor and select Microsoft Forms 2.0 Object Library. Or you can
change to late bind (i.e. use CreateObject)

Just assign the macro to a button or keyboard shortcut and good to go.
Note
you can also change the WorksheetFunction from .Sum to any valid function
(i.e. .Average etc.)

Paul D

I noticed something..if you have an autofilter on, this sub still takes the
value of every cell, not just the visible ones...is it possible to change is
so that it only looks at visible cells?


"PaulD" <nospam wrote in message
...
"Job" wrote in message
...
: Does anyone know how to take the value of the little window on the
bottom
: right of excel (ie you highlight a group of numbers and it shows the sum
or
: avg or whatever you choose.) and copy it to the clipboard?
:
: Cheers!

This should do the trick

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject

MyData.SetText WorksheetFunction.Sum(Selection)
MyData.PutInClipboard

End Sub

In order to run since MyData is earlybind, you must go to tools reference
in
the VBA editor and select Microsoft Forms 2.0 Object Library. Or you can
change to late bind (i.e. use CreateObject)

Just assign the macro to a button or keyboard shortcut and good to go.
Note
you can also change the WorksheetFunction from .Sum to any valid function
(i.e. .Average etc.)

Paul D

Job, using PaulD's example, this should work:

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject

MyData.SetText
WorksheetFunction.Sum(Selection.SpecialCells(xlCel lTypeVisible))
MyData.PutInClipboard

End Sub

HTH--Lonnie M.

Thanks Lonnie

"Lonnie M." wrote in message
oups.com...
Job, using PaulD's example, this should work:

Sub Macro1()
Dim MyData As DataObject
Set MyData = New DataObject

MyData.SetText
WorksheetFunction.Sum(Selection.SpecialCells(xlCel lTypeVisible))
MyData.PutInClipboard

End Sub

HTH--Lonnie M.