Copy Selection Sum to Clipboard

By in Copy and Paste, Keyboard on .

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.

18 thoughts on “Copy Selection Sum to Clipboard

  1. gruf999

    I wasn`t able to reproduce the HTML table issue when pasting into Outlook. But if the HTML isn`t there when pasting into Notepad I`d guess it was Outlook doing something rather than something at source. Not being able to reproduce the issue meant I couldn`t test the potential solution I googled:

    doClip.SetText strText, Application.WorksheetFunction.Sum(Selection)
  2. Dick Kusleika Post author

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

    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. Dick Kusleika Post author

    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. Bob Phillips

    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. Pingback: Excel Roundup 20140505 | Contextures Blog

  7. Chris U

    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?

  8. PJ_in_FL

    I get a “Variable not defined” error on line

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

    What references are needed for “gclsAppEvents”?

    ——————————–
    PJ in (sunny) FL

  9. Dick Kusleika Post author

    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.

  10. Chris U

    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.

  11. Nigel Heffernan

    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:

    http://office.microsoft.com/en-gb/excel-help/subtotal-function-HP010062463.aspx

  12. snb

    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)
                .PutInClipboard
            End With
        End If
    End Sub

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

    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.
    Thanks,
    Terry

  14. Kevin

    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
        .PutInClipboard
    End With
     
    Application.StatusBar = "Selection Copied to clipboard." '* I like to know it did something.

    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax