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

  1. gruf999 says:

    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. Scott says:

    Ooo, I like this one. I can see why this would not be top of mind but would eventually become very handy…

  3. Dick Kusleika says:

    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.

  4. JLeno says:

    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!

  5. Jon Peltier says:

    Why not use
    WorksheetFunction.Sum(Selection.SpecialCells(xlCellTypeVisible))
    to sum the visible cells, and not deal with subtotals?

  6. Dick Kusleika says:

    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.

  7. Bob Phillips says:

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

  8. […] Dick Kusleika shares his shortcut for copying the sum of selected cells to the clipboard. […]

  9. Chris U says:

    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?

  10. PJ_in_FL says:

    I get a “Variable not defined” error on line

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

    What references are needed for “gclsAppEvents”?

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

  11. Dick Kusleika says:

    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.

  12. Dick Kusleika says:

    PJ: That’s part of a logging system to see how much I use each macro. You can read about it and download it at http://dailydoseofexcel.com/archives/2014/03/17/keyboard-shortcut-metrics/.

    Or you can just delete that line because it doesn’t affect how the code works.

  13. Chris U says:

    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.

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

  15. snb says:

    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
  16. Terry says:

    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

  17. Dick Kusleika says:

    Terry: gclsAppEvents is a custom class I added to keep track of which keyboard shortcuts I use the most. You can read about it here http://dailydoseofexcel.com/archives/2014/03/17/keyboard-shortcut-metrics/

  18. Kevin says:

    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

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: