vba code to sum a selected range and then copy for pasting

[DeFacto]

Hi! Is there a way to use vba code to sum a selected range and then copy this value for use in pasting to a different part of a workbook? This is the code I tried:

Sub SumRangeCopy()
Set CurrSum = Application.WorksheetFunction.Sum(Selection)
CurrSum.Copy
End Sub

When I try that code I get the error message: "Compile Error: Object Required"

Any suggestions would be greatly appreciated!

 

[Legacy 55362]

Sub SumRangeCopy()
[A1] = Application.WorksheetFunction.Sum(Selection)
End Sub

 

[DeFacto]

Thanks! However, is there a way to store the value such that I can paste it into a cell of my choice? I.e., the cell or e-mail I paste the value into will change every time.

 

[DeFacto]

I guess another way of saying it is that I'd like to copy the summed value of the selected range to the clipboard such that I can paste said value into a different tab, different workbook, or different application (which will vary each time).

 

[erik.van.geit]

Hi, DeFacto,

try this

Option Explicit

Sub put_in_get_from_clipboard()
'set reference to Microsoft Forms x.x Object Library
Dim MyData As Object
Set MyData = New DataObject
MyData.SetText Application.WorksheetFunction.Sum(Selection)
MyData.PutInClipboard
'MsgBox MyData.GetText
End Sub

in the VBAmenu Tools / References set the required reference

kind regards,
Erik

 

[Legacy 55362]

Sub Store_in_Clipboard()
Dim theData As New DataObject
theData.SetText Format(Application.WorksheetFunction.Sum(Selection))
theData.PutInClipboard
End Sub

 

[DeFacto]

Hey... this is great! Much appreciated... Thanks a lot!

 

[MontanaBlue]

I can't get the clipboard to paste anything other than some question marks in my cells. Everything seems to work. Is there an update with my version of excel. This post does seem outdated. I'm on office 2013. Any help would be appreciated.

This is what I see when I paste.

I read that I need to select this reference.

-- corrupted image removed --

Any help would be appreciated.

 

[MontanaBlue]

I restarted my excel. I then was able to test it again and it worked. Then I tried it on a different book and it gave me the two ?? again. I went back to the book it worked on and it won't work now. Any reason why it would revert back?

 

[gougouna]

I can't get the clipboard to paste anything other than some question marks in my cells. Everything seems to work. Is there an update with my version of excel. This post does seem outdated. I'm on office 2013. Any help would be appreciated.

This is what I see when I paste.
View attachment 23655

I read that I need to select this reference.

-- corrupted image removed --

Any help would be appreciated.

Hi
did you find any solution for this problem ?
Would be kind your answer