The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
(VBA Tip) Copy the SUM of Selected Cells to Another Cell
by Francis Hayes (The Excel Addict)

Remember way back when Excel introduced the AutoCalculate feature, where you could select a bunch of cells and the SUM of those cells would magically appear in the Status Bar?

Automatically show the total of selected cells on the Status Bar

Not long after I began using AutoCalulate, I found that I frequently needed to use the SUM of the highlighted cells elsewhere. I wondered if there was a feature in Excel that I didn't know about that would allow me to copy the SUM of those selected cells into another cell. Unfortunately, the answer was NO.

Copy the total of selected cells
At that time I had been using macros for a number of years. Macros allowed me to do many 'seemingly impossible' things in Excel, so I thought the solution may be through some VBA programming.

Below is a macro I wrote that calculates the SUM of all selected cells that are visible (i.e. excludes cells in hidden or filtered rows) and stores it on the clipboard. You can then simply Paste that value to another cell, to an email or just about anywhere else. I still use this macro several times a week. It sure beats trying to 'remember' the total and the type it into a cell.

Below are the steps you will need to follow to make this feature available to you.

Disclaimer: The following instructions are provided without any warranty about their usability or performance. Although I have made every effort to ensure these will work as described, I cannot guarantee that they can work for every possible computer configuration.

Step 1 - Add a reference to the DataObject

This macro requires the use of an object called the DataObject. To use it you need to add a reference to it in VBA. Don't be intimidated if you don't know what all that means. You simply press ALT+F11 to open the Visual Basic Editor, then f
rom the Tools menu, select References. Check if 'Microsoft Forms 2.0 Object Library' is in the list of Available references. If not, click Browse, select FM20.DLL, click Open and OK. 

Alternatively, temporarily inserting a User Form (Insert, UserForm) in your VBA project automatically adds the reference to the Microsoft Forms 2.0 Object Library.

Step 2 - Add this macro to your Personal Macro Workbook

If you already use a Personal Macro Workbook, in the Visual Basic Editor, open a code module in PERSONAL.XLSB and paste in the following code into the Code window

Sub CopySUM()
    Dim DataObj As New MSForms.DataObject
    On Error GoTo BailOut
    DataObj.SetText Application.Sum(Selection.SpecialCells(xlCellTypeVisible))
End Sub

If you don't have a Personal Macro Workbook, see this tip for instructions on How to Create a Personal Macro Workbook and then come back to continue with Step 3.

Step 3: Add a button to the Quick Access Toolbar to run the macro

1) Right-click the Ribbon and select Customize Quick Access Toolbar;

2) In the ‘Choose commands from’ dropdown, select Macros;

3) Select the macro (CopySum) from the list and click the Add>> button;

4) Click the macro name on the right side of the Excel Options dialog and click the Modify button;

5) Choose a Symbol to use to run the macro from the QAT, change the 'Display name' to CopySUM and click OK;
Copy the total of selected cells
5) Click OK.

Step 4: Test the CopySUM macro

Paste the SUM of selected cells1) Enter some numbers in a worksheet;

2) Select the numbers and note the SUM total in the Status Bar;

3) Hide some of the rows;

4) Select the cells again and notice 
that the SUM total on the Status Bar does not include the hidden values;

5) Click the CopySUM button on the QAT to copy the sum of the selected cells to the Clipboard;

6) Finally, select a blank cell and press CTRL+V to paste (or right-click, Paste).

Would you believe me if I told you that
you can DOUBLE your productivity in Excel?

How about TRIPLE it ? or QUADRUPLE it ? or MORE ?

If I showed you a tip where you could do a task in Excel in five seconds that would typically take you 20 minutes to an hour or more, what would you call that? Productivity on steroids?

...and that's just ONE tip!
"Give me 10 or 15 minutes each week and I will show you how learning a few of my time-saving 'Spreadsheet Tips From An Excel Addict' on a regular basis will seem almost effortless BUT will quickly help you BOOST your productivity in Excel beyond your imagination" - Francis Hayes, The Excel Addict

Get my FREE Weekly Newsletter for FREE
"Spreadsheet Tips From An Excel Addict"

Now being read by more than 35,000+ Excel Addicts all over the world

Enter Your Name Francis Hayes (The Excel Addict) - I want to help you EXCEL !!
Enter Your Primary Email

Copyright 2015
Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.