Bill looks at pasting Values today, in Episode #1521. Using one line of VBA code, Bill shows us how to convert Formulas to Values. Learn how to quickly - and Easily - modify your recorded macro [using the Macro Recorder] to Paste Values with one click.
...Today's Podcast topic may be found in Bill's book, "VBA and Macros: Microsoft Excel 2010" by Bill Jelen and Tracy Syrstad. The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions.
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...Today's Podcast topic may be found in Bill's book, "VBA and Macros: Microsoft Excel 2010" by Bill Jelen and Tracy Syrstad. The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions.
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn excel from MrExcel podcast. Episode # 1521 VBA Paste Value Hey, it was probably a couple weeks ago, I did a couple of podcasts about the invoice templates and how to make the invoice number Auto increment And I put a little Macro in that podcast that would save the invoice out with the new invoice number.
And someone wrote in and said wait before I do that, I want to convert all those formulas to values, and you know what's the code to do that.
Well, it's amazingly simple to convert formulas to values in code. You wouldn't know it from doing the Macro Recorder.
So I'm going to go out here "View" "Macros" "Record Macro" and Let's see "View" "Macros" use "Relative References".
Sorry that's outside of the screen. So now the "Macro Recorder" is running.
I do Ctrl + shift + down arrow to select the range of formulas and then Ctrl + C and then Home "Paste" "Paste Values" or "Paste Special" "Values". It doesn't matter it all gets recorded the same way.
Alright, so we now have those formulas converted to values.
So I'll stop recording and we'll go take a look at that code so Alt F8 and Macro4. I'll Edit that.
Okay, so let's take a look at what's happening here.
This is where we selected from the current cell down to the end of the range.
I have no problem with that, and then does a Selection.Copy and then a PasteSpecial.
And this PasteSpecial line will actually have a whole bunch of different arguments in it. So it says it's gonna paste just the values, the operation is none, so in other words, it's not going to add subtract multiply and divide.
Skip blanks is FALSE and Transpose is FALSE. It's funny that all of that gets recorded.
Just from doing a simple little you know "Paste" "Paste Values" But anyway none of this is really necessary once we have the range identified. So in this case, it's just known as selection. I can say Selection.Value = Selection.Value All right isn't that kind of funny.
So even if there's formulas there. It's going to overwrite those formulas.
That's going to say hey that value is the current value of the selection, so let's give this a try.
Here's my range. Will run that Macro. I didn't assign it to a shortcut key.
Click Run.
and now all of our formulas are gone replaced by value.
So it's really a simple, simple line of code.
The "Macro Recorders" going to tell you to do the "Paste Special" method.
Don't have to do that at all if you just so let's say your invoice is in A1:Z99 .Value = Paste so that Value is equal to the value and bam that converts the formulas to values.
Well hey I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
Learn excel from MrExcel podcast. Episode # 1521 VBA Paste Value Hey, it was probably a couple weeks ago, I did a couple of podcasts about the invoice templates and how to make the invoice number Auto increment And I put a little Macro in that podcast that would save the invoice out with the new invoice number.
And someone wrote in and said wait before I do that, I want to convert all those formulas to values, and you know what's the code to do that.
Well, it's amazingly simple to convert formulas to values in code. You wouldn't know it from doing the Macro Recorder.
So I'm going to go out here "View" "Macros" "Record Macro" and Let's see "View" "Macros" use "Relative References".
Sorry that's outside of the screen. So now the "Macro Recorder" is running.
I do Ctrl + shift + down arrow to select the range of formulas and then Ctrl + C and then Home "Paste" "Paste Values" or "Paste Special" "Values". It doesn't matter it all gets recorded the same way.
Alright, so we now have those formulas converted to values.
So I'll stop recording and we'll go take a look at that code so Alt F8 and Macro4. I'll Edit that.
Okay, so let's take a look at what's happening here.
This is where we selected from the current cell down to the end of the range.
I have no problem with that, and then does a Selection.Copy and then a PasteSpecial.
And this PasteSpecial line will actually have a whole bunch of different arguments in it. So it says it's gonna paste just the values, the operation is none, so in other words, it's not going to add subtract multiply and divide.
Skip blanks is FALSE and Transpose is FALSE. It's funny that all of that gets recorded.
Just from doing a simple little you know "Paste" "Paste Values" But anyway none of this is really necessary once we have the range identified. So in this case, it's just known as selection. I can say Selection.Value = Selection.Value All right isn't that kind of funny.
So even if there's formulas there. It's going to overwrite those formulas.
That's going to say hey that value is the current value of the selection, so let's give this a try.
Here's my range. Will run that Macro. I didn't assign it to a shortcut key.
Click Run.
and now all of our formulas are gone replaced by value.
So it's really a simple, simple line of code.
The "Macro Recorders" going to tell you to do the "Paste Special" method.
Don't have to do that at all if you just so let's say your invoice is in A1:Z99 .Value = Paste so that Value is equal to the value and bam that converts the formulas to values.
Well hey I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
