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

Watch on YouTube

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