Excel - Macro Recorder Won't Save As Pasted Name - Episode 1759
2,626 views · Published 25 July 2013 · 5:29 · Indexed 16 November 2026
Channel: MrExcel.com · 2013 · Education
Microsoft Excel Tutorial: Overcoming Excel Macro Recorder Limitations: Save As PDF with Clever Workarounds | Excel VBA. Welcome back to the MrExcel netcast, sponsored by "livelessons Excel VBA and Macros with MrExcel"! In this episode, we tackle a question from Michael who is trying to record a macro that will save a file as a PDF. However, he is being too clever for the Excel macro recorder and his steps are not being recorded accurately. This is a common issue that many Excel users face when trying to use the macro recorder for more complex tasks. But fear not, we have a solution for you! Michael's steps involve using F2, CTRL+Shift+Home, copy, ESC, Alt F F P, and CTRL+V to copy the active cell and paste it as the file name for the PDF. While this may work manually, the macro recorder is not able to record these steps accurately. Instead, it only records the final file name that was chosen. But don't worry, we have a workaround for this issue that will allow you to achieve the desired result. We will be using a variable to store the file name and then replace the hard-coded file name in the recorded macro with this variable. This way, the macro will be able to use a different file name each time it is run. We also address the issue of copying the data from a specific cell, which is not recorded by the macro recorder. By creating a variable for the cell reference, we can ensure that the macro will always use the correct data, even if it changes in the future. If you're struggling with the macro recorder and want to improve your VBA skills, I highly recommend checking out my book "VBA and Macros" published by QUE. It comes in four different editions for Excel 2003, 2007, 2010, and 2013. Additionally, QUE has also published "VBA and Macros with MrExcel" live lessons, which will help you get up the VBA learning curve and become a pro at using the macro recorder and writing your own code. So don't get discouraged if the macro recorder is not able to record your desired steps accurately. With a little bit of VBA knowledge and some clever workarounds, you can achieve your desired results. Thank you to Michael for sending in this question and for being a part of the MrExcel community. Make sure to tune in for our next netcast where we will be tackling more Excel and VBA challenges. See you next time! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Too Clever For the Excel Macro Recorder (00:17) Dynamic Print Range Issue (00:28) Macro Recorder Limitations (01:12) Macro Recorder's Inability to Record Copying to Clipboard (01:44) Michael's Clever Solution (02:24) Solution: Creating a Variable (02:41) Recording a Macro with a Variable (03:24) Using the Recorded Macro with Different Data (04:09) Michael's Development on the VBA Learning Curve (04:51) Thanking Michael and Promoting Resources (05:14) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Copy and paste in Excel macro Excel file saved as PDF Excel VBA and Macros Live lessons VBA and Macros with MrExcel Lotus macro recorder Macro recorder limitations Saving Excel file with dynamic name Too Clever For the Excel Macro Recorder Variable in VBA VBA and Macros book by Tracy service(?) VBA learning curve Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153579/ 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.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920