Copy Selection Sum to Clipboard

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

18 thoughts on “Copy Selection Sum to Clipboard

  1. I wasnt able to reproduce the HTML table issue when pasting into Outlook. But if the HTML isnt there when pasting into Notepad Id guess it was Outlook doing something rather than something at source. Not being able to reproduce the issue meant I couldnt test the potential solution I googled:

    doClip.SetText strText, Application.WorksheetFunction.Sum(Selection)

  2. Hmmm, I couldn’t reproduce it in Outlook either. I remember having to paste stuff into Notepad so it would be text, then copy and paste into Outlook. But apparently I don’t remember the details.

  3. Outlook makes a table from the data if you paste more than one cell; if you paste just one cell it pastes the value as ‘normal’ text.

    Personally, I use Ctrl+Alt+V, End, to paste as unformatted text, but i’m gonna give this macro a chance. Simple and elegant!

  4. I hadn’t thought about Special Cells. Is there any difference between a hidden row and a filtered row? I know something treats them differently, but I don’t remember what.

  5. Bot SUBTOTAL(9,… and SUBTOTAL(109,… ignore filtered values. SUBTOTAL(9, … includes hidden values, SUBTOTAL(109,… ignores them (unless there is also a filter, in which case 9 also ignores them).

  6. I am using Excel 2007 and I have an add-in that I use like a personal macro workbook. I have other macros in there saved as icons on my QAT so that I can get to them quickly, which I wanted to do with this. However, when I try to run from the QAT, I get an error message: “Cannot run the macro [filepath and macro here]. The macro may not be available in this workbook or all macros may be disabled.”

    I do not get that message with other macros from my add-in that I link to with the QAT. I am able to run the macro from the VBE. Any idea why/workaround? Is it something to do with using the MSFORMS reference?

  7. I get a “Variable not defined” error on line

    gclsAppEvents.AddLog “^+c”, “CopySum”

    What references are needed for “gclsAppEvents”?

    PJ in (sunny) FL

  8. Not sure Chris U. I just put it on my QAT and it worked as expected. Did you put it in a worksheet module by mistake? Or change it to a private sub? Those seem unlikely, but I always check the easy stuff first.

  9. Nope, neither of those.

    I just removed and re-added it to the QAT and now it works. Who knows?! Thanks for sharing the code.

  10. Is it 9 or 109 in the ‘Function_num’ parameter?

    doClip.SetText Application.WorksheetFunction.Subtotal(9, Selection)

    Microsoft's documentation suggests that it's 109, if you want to respect filtering and rows hidden by the user:
    Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.

    Details here:

  11. Wouldn’t it be more obvious to test before doing anything else ?

    Sub M_sum()
    If TypeName(Selection) = "Range" Then
    With New DataObject
    .SetText Application.Sum(Selection)
    End With
    End If
    End Sub

    Sub M_subtotal()
    If TypeName(Selection) = "Range" Then
    With New DataObject
    .SetText Application.Subtotal(109, Selection)
    End With
    End If
    End Sub

  12. Hi,
    This is a nice shortcut.
    Can someone please tell me more about “gclsAppEvents.AddLog”? I’ve searched for more information but can’t find anything useful.
    And while I’m confessing my ignorance, how about the “^+c”? I took it to mean control + c but this doesn’t work for me.

  13. This is great. I also found there were times when I wanted to copy a range of cells as just text. I have this one in my Personal.xlsb. I have a startup routine that sets my shortcuts from entries I make in the sheet (Not shown here).

    Sub CopyRangeAsText()

    '* Gets the text in any number of cells and builds a string, then loads the clipboard

    Dim CopyRng As Range
    Dim Cell As Range
    Dim Clipboard As Variant

    For Each Cell In Selection
    If Len(Cell.Value) > 0 Then
    Clipboard = Clipboard & " " & Cell.Formula
    End If
    Next Cell

    With New MSForms.DataObject
    .SetText Clipboard
    End With

    Application.StatusBar = "Selection Copied to clipboard." '* I like to know it did something.

    End Sub

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.