Michael is trying to use the Excel macro recorder in a clever way today. He has a formula that calculates a path and file name. In the macro recorder, he uses F2, Ctrl+Shift+Home, Ctrl+C and Esc to get that name on the clipboard. He then pastes it into the SaveAs box. Works great in Excel, but the macro recorder won't handle it. In this episode, you will see how to take the recorded code and modify it to Save As with the result of the formula.
Transcript of the video:
Bill: The MrExcel podcast is sponsored by "livelessons Excel VBA and Macros with MrExcel”!
Learn Excel from MrExcel podcast, episode 1759 - Too Clever For the Excel Macro Recorder!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question is sent in by Michael. Now we've already seen this file once this week, where we had the dynamic print range, but Michael is trying to record a macro, and frankly Michael is being just too clever for the macro recorder, for the Excel macro recorder anyway. These exact same steps would have worked back in the Lotus macro recorder all the way back in 1985, but they're not going to work in Excel. So Michael is trying to record a macro that's going to save this file as a PDF. So let's turn on the macro recorder, click OK. Now I'm actually going to let Michael describe the exact steps that he's doing. Michael take it away!
Michael: I want in a macro to do this. I wanted to do, to copy what I have in the cell that's active right now. So to do that I do F2, CTRL+Shift+Home, copy, ESC, Alt F F P, and CTRL+V. And when I do that manually, it works. I can just hit Enter, and I publish the rent receipt. But when I copy that in a macro, it doesn't work.
Bill: OK, that was really, really clever, let's stop recording. So Michael was trying to save the file with this name here as a PDF, and by using F2 CTRL+Shift+Home, he selects the whole range, puts it on the clipboard with CTRL+C, Esc, and then it goes through the commands, and it was actually different in Excel 2010, so Alt F D and then P and then A, to get to the save as PDF, and then he just CTRL+V to paste, and paste it in. But the macro recorder is not going to record those steps, it's not going to record the action of copying that to the clipboard. Let's go take a look at the macro that we get.
Alright, despite the fact that Michael went through all those great gyrations of F2, CTRL+Shift+Home, CTRL+C, before he went out, all that gets recorded is the ultimate name that he chose.
None of those gyrations with copying the characters from the cell get recorded. Ah, but we can do that. When Michael is copying to the clipboard, he's essentially putting something in a variable, and so I'm going to create a variable here called FN for filename, = Activesheet.Range, I'll put the range name in there, .Value . I just have to go back and look at Excel, and see that the data is coming from I7. And in fact, I can even hypothesize that Michael really wants the data to come from this formula here in I5. His macro must be doing a copy and paste Special Values there, before he started to describe what's going on. So I5 is the important part, so we'll say I5 here.
And then, is the recorded macro completely worthless, do we have to throw it away? No!
Here, where they've hard-coded exactly where they want to go, we are going to replace that with our variable name. So make sure include the quotes on both ends, and that should do it. Alright so now, if we have some different data here, I'll just put in something, and that file name changes, and we run our macro, Alt+F8, HowtosaveAsPDF, Run, and we go out and look in the folder, sure enough there is the one that was created when we recorded the macro, and there is the one that was created as we ran the macro with the new value.
Alright, so I understand exactly at the point where Michael is in his development up the VBA learning curve. He's using the macro recorder, he ran into a huge problem that he needed to be very clever in getting it to use a different file name each time, so he built that in a formula in Excel, of course, you know, we're all great at Excel, and then tried to have the macro recorder record that CTRL+C and CTRL+V, and just, no way it's going to happen.
But, here we are, creating our first variable, the macro recorder will never create a variable for you as you're putting something on a clipboard.
You're in essence, storing it in a variable, and then using that variable inside the recorded code to replace the hard-coded file.
Alright, there you have it. I want to thank Michael for sending that question in, I appreciate you sitting in the- he actually sent in a video, I just pulled the audio from there.
To get up the VBA learning curve, I have Tracy service(?), and I have "VBA and Macros", there's four different editions: 2003, 2007, 2010, and 2013, published by QUE. Also QUE published live lessons "VBA and Macros with MrExcel", gets you up this curve from the macro recorder.
You know, we still do a lot of recorded code, but just have to know when you have to jump out of the macro recorder, and go add a couple of lines to your macro to make it work better.
See you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1759 - Too Clever For the Excel Macro Recorder!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question is sent in by Michael. Now we've already seen this file once this week, where we had the dynamic print range, but Michael is trying to record a macro, and frankly Michael is being just too clever for the macro recorder, for the Excel macro recorder anyway. These exact same steps would have worked back in the Lotus macro recorder all the way back in 1985, but they're not going to work in Excel. So Michael is trying to record a macro that's going to save this file as a PDF. So let's turn on the macro recorder, click OK. Now I'm actually going to let Michael describe the exact steps that he's doing. Michael take it away!
Michael: I want in a macro to do this. I wanted to do, to copy what I have in the cell that's active right now. So to do that I do F2, CTRL+Shift+Home, copy, ESC, Alt F F P, and CTRL+V. And when I do that manually, it works. I can just hit Enter, and I publish the rent receipt. But when I copy that in a macro, it doesn't work.
Bill: OK, that was really, really clever, let's stop recording. So Michael was trying to save the file with this name here as a PDF, and by using F2 CTRL+Shift+Home, he selects the whole range, puts it on the clipboard with CTRL+C, Esc, and then it goes through the commands, and it was actually different in Excel 2010, so Alt F D and then P and then A, to get to the save as PDF, and then he just CTRL+V to paste, and paste it in. But the macro recorder is not going to record those steps, it's not going to record the action of copying that to the clipboard. Let's go take a look at the macro that we get.
Alright, despite the fact that Michael went through all those great gyrations of F2, CTRL+Shift+Home, CTRL+C, before he went out, all that gets recorded is the ultimate name that he chose.
None of those gyrations with copying the characters from the cell get recorded. Ah, but we can do that. When Michael is copying to the clipboard, he's essentially putting something in a variable, and so I'm going to create a variable here called FN for filename, = Activesheet.Range, I'll put the range name in there, .Value . I just have to go back and look at Excel, and see that the data is coming from I7. And in fact, I can even hypothesize that Michael really wants the data to come from this formula here in I5. His macro must be doing a copy and paste Special Values there, before he started to describe what's going on. So I5 is the important part, so we'll say I5 here.
And then, is the recorded macro completely worthless, do we have to throw it away? No!
Here, where they've hard-coded exactly where they want to go, we are going to replace that with our variable name. So make sure include the quotes on both ends, and that should do it. Alright so now, if we have some different data here, I'll just put in something, and that file name changes, and we run our macro, Alt+F8, HowtosaveAsPDF, Run, and we go out and look in the folder, sure enough there is the one that was created when we recorded the macro, and there is the one that was created as we ran the macro with the new value.
Alright, so I understand exactly at the point where Michael is in his development up the VBA learning curve. He's using the macro recorder, he ran into a huge problem that he needed to be very clever in getting it to use a different file name each time, so he built that in a formula in Excel, of course, you know, we're all great at Excel, and then tried to have the macro recorder record that CTRL+C and CTRL+V, and just, no way it's going to happen.
But, here we are, creating our first variable, the macro recorder will never create a variable for you as you're putting something on a clipboard.
You're in essence, storing it in a variable, and then using that variable inside the recorded code to replace the hard-coded file.
Alright, there you have it. I want to thank Michael for sending that question in, I appreciate you sitting in the- he actually sent in a video, I just pulled the audio from there.
To get up the VBA learning curve, I have Tracy service(?), and I have "VBA and Macros", there's four different editions: 2003, 2007, 2010, and 2013, published by QUE. Also QUE published live lessons "VBA and Macros with MrExcel", gets you up this curve from the macro recorder.
You know, we still do a lot of recorded code, but just have to know when you have to jump out of the macro recorder, and go add a couple of lines to your macro to make it work better.
See you next time for another netcast from MrExcel!
