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.
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.
Sub CopySUM()
Dim DataObj As New MSForms.DataObject
On Error GoTo BailOut
DataObj.SetText Application.Sum(Selection.SpecialCells(xlCellTypeVisible))
DataObj.PutInClipboard
BailOut:
End Sub
Dim DataObj As New MSForms.DataObject
On Error GoTo BailOut
DataObj.SetText Application.Sum(Selection.SpecialCells(xlCellTypeVisible))
DataObj.PutInClipboard
BailOut:
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;
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).
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 |
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.