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.
I wasn
t 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)Ooo, I like this one. I can see why this would not be top of mind but would eventually become very handy…
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.
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!
Why not use
WorksheetFunction.Sum(Selection.SpecialCells(xlCellTypeVisible))
to sum the visible cells, and not deal with subtotals?
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.
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).
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?
I get a “Variable not defined” error on line
gclsAppEvents.AddLog “^+c”, “CopySum”
What references are needed for “gclsAppEvents”?
——————————–
PJ in (sunny) FL
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.
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.
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.
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
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
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
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/
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