As you know, you can customize the Quick Stats to show you Sum, Average, Count, Max, Min, and Count Numeric. But...what if you wanted to copy those values to the clipboard so you could paste them somewhere?
I had fun with today's podcast, finding code from Nate Oliver, Juan Pablo Gonzalez and then finally Chip Pearson to solve the last part of the problem. Just as Szilvia Juhasz's closing theme music started to play, I thought to myself... "this would be a whole lot better if it would paste as formulas instead of static values", so I paused the recording and tried it out.
This podcast will be useful for anyone who needs to copy a VBA variable to the Excel clipboard for later pasting.
See the accompanying blog post: Copy the Quick Stats Values to the Clipboard in order to copy the code or download the workbook.
I had fun with today's podcast, finding code from Nate Oliver, Juan Pablo Gonzalez and then finally Chip Pearson to solve the last part of the problem. Just as Szilvia Juhasz's closing theme music started to play, I thought to myself... "this would be a whole lot better if it would paste as formulas instead of static values", so I paused the recording and tried it out.
This podcast will be useful for anyone who needs to copy a VBA variable to the Excel clipboard for later pasting.
See the accompanying blog post: Copy the Quick Stats Values to the Clipboard in order to copy the code or download the workbook.
Transcript of the video:
MrExcel podcast sponsor by Easy-XL.
Learn Excel from MrExcel Podcast Episode 1894: Copy the Quick Stats Bar to the Clipboard as Static Values.
All right, gang, this one, I love this one.
This was a great idea.
I was doing a seminar somewhere, a Power Excel seminar, and someone said, “Hey, you know, we have the Quick Stats bar down here, right?” It usually shows the AVERAGE and some of the selected cells and if we would right click we could of course add Count, Numerical Count, Min and Max which is one of the things I suggest during the Power Excel seminar.
And someone said, “Hey, here's what we want.
We want to be able to come down here and copy those values to the clipboard, put them on the clipboard so we can then select some other cells and paste, and the paste would paste as static values.” I’m like, “Hey, that's a great idea.” Now, they're in the seminar.
I said, “Well, we'll have to get back to you and, you know, well think about it.” And so today working on the podcast, I started to think about that.
And if we select a bunch of cells, I'm going to switch over here to VBA with ALT+F11 and then Insert module.
I started a little program here called Sub CopyQuickStatsToClipboard and I'm going to call this 1 because this is my first try.
Now, I get tired typing Application.WorksheetFunctions so I set that to a variable and I built a string here.
MS stands for My String, alright?
So the string starts out with the word “AVERAGE:” and then I've never used this before but sure enough it worked vbTab so I type AVERAGE and then a Tab character and then I put in the Application.WorksheetFunction.AVERAGE of the selection followed up with vbCr, meaning carriage return.
And that underscore there that's a continuation character so this is really all one big long line of code.
And you can see what I'm doing here is I'm building a very long text string that has the word AVERAGE then a Tab and the actual AVERAGE or COUNT and a Tab in the actual count - Count A. The Numerical Count and Tab and the Actual Count.
Min tab and the Min/Max Tab and the Max.
All right, so all of that is really cool, right?
And when I run it, it's no hassle to show the results in a message box, right?
So there's the there's the text string, numerical count was a little bit too long but it's doing the right thing.
Then I said, all right how do I get that string onto the clipboard?” MS.Copy does not work, no.
All right, so go out to Google or Bing, your choice, and the first place that I landed was at my own message board and lots of ideas here by my old, old friend, Juan Pablo, the one of the first consultants to ever work for me and from our dear departed, Nate Oliver, who was one of the wizards at the board.
But their ideas actually then led me to another Microsoft MVP, Chip Pearson from Kansas City who had this tiny little bit of code and Chip’s code is the one that actually does what we need.
Now, I'm going to come back here to Module 1, so we have a different module.
This is the one that's actually going to work, and you see that all of that is the same thing I had before but to make Chip’s code work we have to go to Tools References and this Microsoft Forms 2.0 Object Library was not selected.
It was about right there, you don't have to scroll down far to get to it but you have to choose that so add that reference.
Very important; it's not going to work if you don't do that.
And then Dim DataObj as new MSForms.DataObject SetText and I then load it in the string so I have this string called MS and then finally Data.Obj.PutInClipboard.
All right, so check this out, and of course the message box is here so we click RUN and then I choose a new cell and Ctrl-V to paste and sure enough it does the right thing, it puts it in a six row by two column range as static values.
What a great idea from this person in the seminar and thanks to Juan Pablo and Nate and Chip a very, very cool way to solve that problem.
Hey, wait, wait.
Don’t go away.
Did you hear that long pause?
That long pause I said.
Wait a second, this is great with static values, but I wonder if we could make it in to formulas, so I paused the video and I came back here and copied the formula down.
I didn't want you to watch me do it because I didn't think I was going to work and right here MA = Selection.Address.
This would only work if you chose one contiguous area and then actually write the formulas and that get written as a formula and we'd have to the course do it here and here and here and here so let's run this as formulas, and then come back and we'll paste Ctrl V and sure enough that gets written as a formula.
That way if something here would change, then those quick stats would continue to work.
So, there's two different Macros depending on whether you want it written as a static value, which was the request from the seminar or as formulas.
This one will work with non-contiguous range as this one has to be a single contiguous range, two different ways to go.
All right, well hey I want to thank you for stopping.
We'll see you next time for another netcast of MrExcel.
Learn Excel from MrExcel Podcast Episode 1894: Copy the Quick Stats Bar to the Clipboard as Static Values.
All right, gang, this one, I love this one.
This was a great idea.
I was doing a seminar somewhere, a Power Excel seminar, and someone said, “Hey, you know, we have the Quick Stats bar down here, right?” It usually shows the AVERAGE and some of the selected cells and if we would right click we could of course add Count, Numerical Count, Min and Max which is one of the things I suggest during the Power Excel seminar.
And someone said, “Hey, here's what we want.
We want to be able to come down here and copy those values to the clipboard, put them on the clipboard so we can then select some other cells and paste, and the paste would paste as static values.” I’m like, “Hey, that's a great idea.” Now, they're in the seminar.
I said, “Well, we'll have to get back to you and, you know, well think about it.” And so today working on the podcast, I started to think about that.
And if we select a bunch of cells, I'm going to switch over here to VBA with ALT+F11 and then Insert module.
I started a little program here called Sub CopyQuickStatsToClipboard and I'm going to call this 1 because this is my first try.
Now, I get tired typing Application.WorksheetFunctions so I set that to a variable and I built a string here.
MS stands for My String, alright?
So the string starts out with the word “AVERAGE:” and then I've never used this before but sure enough it worked vbTab so I type AVERAGE and then a Tab character and then I put in the Application.WorksheetFunction.AVERAGE of the selection followed up with vbCr, meaning carriage return.
And that underscore there that's a continuation character so this is really all one big long line of code.
And you can see what I'm doing here is I'm building a very long text string that has the word AVERAGE then a Tab and the actual AVERAGE or COUNT and a Tab in the actual count - Count A. The Numerical Count and Tab and the Actual Count.
Min tab and the Min/Max Tab and the Max.
All right, so all of that is really cool, right?
And when I run it, it's no hassle to show the results in a message box, right?
So there's the there's the text string, numerical count was a little bit too long but it's doing the right thing.
Then I said, all right how do I get that string onto the clipboard?” MS.Copy does not work, no.
All right, so go out to Google or Bing, your choice, and the first place that I landed was at my own message board and lots of ideas here by my old, old friend, Juan Pablo, the one of the first consultants to ever work for me and from our dear departed, Nate Oliver, who was one of the wizards at the board.
But their ideas actually then led me to another Microsoft MVP, Chip Pearson from Kansas City who had this tiny little bit of code and Chip’s code is the one that actually does what we need.
Now, I'm going to come back here to Module 1, so we have a different module.
This is the one that's actually going to work, and you see that all of that is the same thing I had before but to make Chip’s code work we have to go to Tools References and this Microsoft Forms 2.0 Object Library was not selected.
It was about right there, you don't have to scroll down far to get to it but you have to choose that so add that reference.
Very important; it's not going to work if you don't do that.
And then Dim DataObj as new MSForms.DataObject SetText and I then load it in the string so I have this string called MS and then finally Data.Obj.PutInClipboard.
All right, so check this out, and of course the message box is here so we click RUN and then I choose a new cell and Ctrl-V to paste and sure enough it does the right thing, it puts it in a six row by two column range as static values.
What a great idea from this person in the seminar and thanks to Juan Pablo and Nate and Chip a very, very cool way to solve that problem.
Hey, wait, wait.
Don’t go away.
Did you hear that long pause?
That long pause I said.
Wait a second, this is great with static values, but I wonder if we could make it in to formulas, so I paused the video and I came back here and copied the formula down.
I didn't want you to watch me do it because I didn't think I was going to work and right here MA = Selection.Address.
This would only work if you chose one contiguous area and then actually write the formulas and that get written as a formula and we'd have to the course do it here and here and here and here so let's run this as formulas, and then come back and we'll paste Ctrl V and sure enough that gets written as a formula.
That way if something here would change, then those quick stats would continue to work.
So, there's two different Macros depending on whether you want it written as a static value, which was the request from the seminar or as formulas.
This one will work with non-contiguous range as this one has to be a single contiguous range, two different ways to go.
All right, well hey I want to thank you for stopping.
We'll see you next time for another netcast of MrExcel.
