Excel Tip #1 - Copy/Paste Sum of Selected Cells

How to Copy/Paste the Sum of Selected Cells in Excel

I've booked about three hundred hours plus of work in March. This is not by choice, it's just the nature of the business, but I am always looking for ways to be more efficient. As an auditor, there are a lot of menial tasks that can be eliminated with macros in excel files. We are interested in totals and it's a pain to have to copy a set of numbers into another worksheet and sum them up to get the number you need. Typically when you highlight a set of numbers the total will appear on the bottom left hand corner. I wanted to be able to copy that total directly and paste it in a separate spreadsheet.

Here is a macro that will help you do that.

Refer to the below five step process:

1) Hit Alt + F11 (this will open the VBE Window)

2) Select 'Module' from the 'Insert Menu' and paste the code below.

Sub mySum()
Dim MyDataObj As New DataObject
MyDataObj.SetText Application.Sum(Selection)
MyDataObj.PutInClipboard
End Sub

3) Select 'References' from the 'Tool Menu' and make sure 'Microsoft Forms 2.0 Object Library' is selected. If it's not listed then click 'browse' and select 'Fm20.dll'

4) Close the VBE window

5) Hit Alt + F8, select 'mySum', click 'Options' and set your shortcut key.