(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?
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.
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 from 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.
Dim DataObj As New MSForms.DataObject
On Error GoTo BailOut
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;
5) Click OK.
Step 4: Test the CopySUM macro
1) 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).
|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.