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

Mar 28, 2010 -

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.

22 comments :

JustinWebPro said...

This solution was exactly what I was looking for! It was clearly written, easy to implement, and required no guess work on my part. Thanks for sharing your knowledge Kevin!

-Justin G, Minneapolis

Heinrich von Horsten said...

Thanks kevin. From fellow auditor

Unknown said...

Hey thanks for the post - has saved me tons of time.

BUT if you have a filter on your data that you want to sum, you need to change
"MyDataObj.SetText Application.Sum(Selection)"
to
"MyDataObj.SetText Application.Subtotal(9, Selection)"

Unknown said...

Thanks for sharing the tip and explaining it so well as well ;)

Kristan K said...

Very helpful!

I also went searching for a way to copy the sum only of selected cells and found I put the following in, and it worked.

MyDataObj.SetText Application.Sum(Selection.SpecialCells(xlCellTypeVisible))

Anonymous said...

Hi,
Best instruction ever, worked perfectly.
Thanks!

Ari,

Anonymous said...

Thanks, Worked perfectly. Saved me hours!

Anonymous said...

Incredibly helpful. Thanks for the very clear instructions!

Unknown said...

Thanks for sharing your very helpful knowledge. Zeljko

Anonymous said...

Thank you so much!!

B.N. Waite said...

Thank you so much!! Just what i was looking for.

Anonymous said...

Very cool. Just what I need. Thank you!

Anonymous said...

Thanks this is great!

Viccy Baker said...

Wonderful!!! But can you give us a way to copy and paste the AVERAGE of selected cells?

Viccy Baker said...

Ok I simply put average wherever you had put sum and it worked!!!!!!!!!!!!!!!
Thank you so much

Anonymous said...

Very nice!

cuppop f said...

Oh my, this is brilliant!! Thank you so much for sharing!

tim said...

That is a very useful post - thank you!

Anonymous said...

I usually don't leave a comment on blog posts that have proved useful, but I must say that you are a productivity genius!

Unknown said...

Thanks Superb !

Anonymous said...

Great, thank you!

Anonymous said...

I've been looking for this for years -- thanks -- works just as advertised!

 
Copyright © 2007- StockKevin. Disclaimer. All Rights Reserved.