Last month I posted some metrics on the keyboard shortcuts I use. One of the pieces of code that I could not link to (because I’ve never posted it) is CopySum. I don’t remember what prompted me to write this little procedure, but it has been surprisingly useful. It sums the selected cells and puts that sum into the clipboard. That’s all it does.
Sub CopySum()
Dim doClip As MSForms.DataObject
On Error Resume Next
gclsAppEvents.AddLog "^+c", "CopySum"
Set doClip = New MSForms.DataObject
If TypeName(Selection) = "Range" Then
doClip.SetText Application.WorksheetFunction.Sum(Selection)
doClip.PutInClipboard
End If
End Sub
If I want to get a one-off sum of something and use it in another program, this comes in handy. I could SUM in a cell, copy that cell, paste it, and delete it. If I paste into Notepad, it’s fine, but if I try to paste into Outlook or even Gmail those programs try to get fancy and make an HTML table. Sometimes I just want the text.
One shortcoming of this procedure is that it doesn’t do well with filtered cells. The Selection includes both visible and hidden cells, but I probably only want visible. I’m changing the code to
doClip.SetText Application.WorksheetFunction.Subtotal(9, Selection)
so it works with filtered data.