Highlighting to produce sum

[Ben C]

Hi All,

When you highlight cells and it shows the sum of them at the bottom right of the screen, is there a way of copying this value into the clipboard to paste elsewhere?

Many Thanks for your help

Regards Ben

 

[erik.van.geit]

Hi, Ben,

try this

Option Explicit

Sub sum_to_clipboard()


'*************************************************************************************
'* SET REFERENCE to "Microsoft Forms x.x Object Library" (VBA-menu Tools/References) *
'* else you will get an error on line "Set myData = New DataObject"                  *
'*************************************************************************************

Dim MyData As Object
Set MyData = New DataObject
MyData.SetText Application.WorksheetFunction.Sum(Selection)
MyData.PutInClipboard

End Sub

this way you will be able to paste into any application

kind regards,
Erik

 

[Ben C]

Hi Erik,

Thanks for your reply, unfortunatly I haven't a clue how to carry this out!
Can you advise please?

Many Thanks

Regards

Ben

 

[erik.van.geit]

start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste your code:

to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)

assign shortcutkey
menu Tools/Macro/Macros
select your macro
click options
choose a character as shortcut: example T
to run the macro press Ctrl+Shift+T

How to learn with example-codes...
1. use the macro recorder to see how code is generated (such code will need some "cleaning" afterwards)
2. "step" through it with the function key F8, while observing what's going on on your sheet
3. click on an item and hit F1 to let popup the help
4. add some steps to see what's going on in your code
example
add: MsgBox "you're currently on this sheet :" & ActiveSheet.Name & Chr(10) & Cell(i, j).Address & "will be filled with the value" & tmp
or before running click in the line and hit F9 (inserting a breakpoint)

I hope this will help you to grow in this intresting stuff
Erik

 

[Ben C]

ADVERTISEMENT

Hi Erik,

I think I should be OK with that

Many Thanks for your help

Regards

Ben

 

[Ben C]

Got it working

Thanks Erik

 

[erik.van.geit]

you're welcome
loved to help