Pablo sends in a great question: why can't you copy a range of cells and paste only the total of those cells in a new cell? Microsoft should really add this functionality, but in the meantime, 2 lines of macro code solve the problem. Episode 936 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Well, I want to address a question today sent in by Pablo and, boy, this is a great question from Pablo.
I wonder why Microsoft never thought of this bit of functionality.
Pablo says, you know, if you select some numeric cells in the spreadsheet, you can look down here in the status bar and see the total of those cells, 81074.
He says, why can't I press a key stroke and copy that total to the clipboard, and then I could come over to another cell and paste that total, and I said, wow, that's a great idea.
Now, there's no way that I know of in Excel to do that but we can do that using just a tiny bit of macro code.
So, let's go into the Office button, EXCEL OPTIONS, SHOW DEVELOPER TAB IN THE RIBBON, click OK, and now we can go to the DEVELOPER tab and go over to VISUAL BASIC.
I want to show you the 2 bits of code that I wrote here.
Macro1, this is like the copy macro.
So, LASTSUM, that’s a variable, is = to APPLICATION.WORKSHEETFUNCTION.SUM SELECTION.VALUE, and then the second macro is ACTIVECELL.VALUE is = to LASTSUM.
Now, in order to make VBA remember this number between the 2 macros, we have to have this global declaration at the top -- GLOBAL LASTSUM AS LONG.
That says, hey, this variable is going to live even after this macro finishes.
That's very unusual.
Most of the time, variables, you know, they go away as soon as the macro finishes.
Now, to make this be a little bit better, I came here to the MACROS dialogue and, for Macro1, I went to OPTIONS and said, okay, we're going to make this be CONTROL+SHIFT+C.
Usually, CONTROL+C is copy.
I assign this to CONTROL+SHIFT+C, and then, for Macro2, the paste macro, OPTIONS, we go to CONTROL+SHIFT+V.
So, let's try it.
We’ll select some cells, I'll do CONTROL+SHIFT+C, and then go to a new cell and do CONTROL+SHIFT+V, and it pastes the total of the original cells.
Great bit of functionality.
Microsoft, you should add this to Excel 2012 or whatever the next one's going to be after Excel 2010, but, in the meantime, just a couple of tiny little macros there, one line each as well as a global declaration, will solve this problem.
Now, what would have been really, really cool, and I couldn't figure out how to do it, in the status bar, it's possible to change that sum to something else.
You can change it to an average or whatever.
You can right-click and show an AVERAGE instead of the SUM.
Actually, in 2007, you can show them all -- AVERAGE, COUNT, NUMERICAL COUNT, MIN, and MAX.
I would have loved to have been able to figure out which one was down there and done the appropriate function in the first macro, but it doesn't seem that Microsoft lets us know what the status bar is showing.
They'll tell us what message is down here in the left side of the status bar but not which quick sum feature is down there.
That's too bad.
You know, if Pablo wanted to do averages or if you want to do averages, you could always have a couple of different macros -- one that does SUM, one that does AVERAGE, and so on.
So, there you have it.
Great question from Pablo.
In just a couple lines of macro code, that helped solve it.
I want to thank you for watching.
We'll see you next time for another netcast from MrExcel.
Well, I want to address a question today sent in by Pablo and, boy, this is a great question from Pablo.
I wonder why Microsoft never thought of this bit of functionality.
Pablo says, you know, if you select some numeric cells in the spreadsheet, you can look down here in the status bar and see the total of those cells, 81074.
He says, why can't I press a key stroke and copy that total to the clipboard, and then I could come over to another cell and paste that total, and I said, wow, that's a great idea.
Now, there's no way that I know of in Excel to do that but we can do that using just a tiny bit of macro code.
So, let's go into the Office button, EXCEL OPTIONS, SHOW DEVELOPER TAB IN THE RIBBON, click OK, and now we can go to the DEVELOPER tab and go over to VISUAL BASIC.
I want to show you the 2 bits of code that I wrote here.
Macro1, this is like the copy macro.
So, LASTSUM, that’s a variable, is = to APPLICATION.WORKSHEETFUNCTION.SUM SELECTION.VALUE, and then the second macro is ACTIVECELL.VALUE is = to LASTSUM.
Now, in order to make VBA remember this number between the 2 macros, we have to have this global declaration at the top -- GLOBAL LASTSUM AS LONG.
That says, hey, this variable is going to live even after this macro finishes.
That's very unusual.
Most of the time, variables, you know, they go away as soon as the macro finishes.
Now, to make this be a little bit better, I came here to the MACROS dialogue and, for Macro1, I went to OPTIONS and said, okay, we're going to make this be CONTROL+SHIFT+C.
Usually, CONTROL+C is copy.
I assign this to CONTROL+SHIFT+C, and then, for Macro2, the paste macro, OPTIONS, we go to CONTROL+SHIFT+V.
So, let's try it.
We’ll select some cells, I'll do CONTROL+SHIFT+C, and then go to a new cell and do CONTROL+SHIFT+V, and it pastes the total of the original cells.
Great bit of functionality.
Microsoft, you should add this to Excel 2012 or whatever the next one's going to be after Excel 2010, but, in the meantime, just a couple of tiny little macros there, one line each as well as a global declaration, will solve this problem.
Now, what would have been really, really cool, and I couldn't figure out how to do it, in the status bar, it's possible to change that sum to something else.
You can change it to an average or whatever.
You can right-click and show an AVERAGE instead of the SUM.
Actually, in 2007, you can show them all -- AVERAGE, COUNT, NUMERICAL COUNT, MIN, and MAX.
I would have loved to have been able to figure out which one was down there and done the appropriate function in the first macro, but it doesn't seem that Microsoft lets us know what the status bar is showing.
They'll tell us what message is down here in the left side of the status bar but not which quick sum feature is down there.
That's too bad.
You know, if Pablo wanted to do averages or if you want to do averages, you could always have a couple of different macros -- one that does SUM, one that does AVERAGE, and so on.
So, there you have it.
Great question from Pablo.
In just a couple lines of macro code, that helped solve it.
I want to thank you for watching.
We'll see you next time for another netcast from MrExcel.
