© Roy Cox (2005 - 2020)

.

Recording a macro

You can use Excel's macros to automate tasks that you find yourself doing repeatedly. There are several ways to create macros, also called "subs" using Visual Basic for Applications (VBA), the programming language for Excel. You can copy some code from a friend or a Web site or you can write the VBA code yourself. A good way to get started is to record a simple macro using the Macro Recorder. Since the introduction of Excel 2007 you can open the Macro Recorder from the Developer Tab. There is also a button on the status bar that you can use to quickly record a macro which is just a shortcut to the "Record Macro" button on the Developer Tab. Once clicked it will change to a stop recording button, click it again to stop recording..

Using the macro Recorder

Using either method will display the Macro Recorder. 1 . The first step is to give your macro a descriptive name. The default name of your macro will be "Macro1", "Macro2", and so on depending on the number of macros that have already been recorded in that workbook. Macro names must begin with a letter and cannot resemble cell addresses (i.e. A1, D4, etc). You cannot use spaces (you can use the underscore character instead, e.g. My_Macro), dashes (hyphens) or other special characters in your macro name. If you try then you will get an error message - "That name is invalid". You can also include numbers in your macro names. 2 . Next decide where to store the macro. You can save it in the current workbook (ThisWorkbook). This choice will attach this macro to the current workbook, and it will only be accessible when this workbook is open. If you want to be able to use the macro from any workbook on that computer, choose the "Personal Macro Workbook". You can also decide to record to a new workbook. 3 . You have the option to add a keyboard shortcut to run your macro. Simply enter a letter in the Shortcut key box, either when you create the macro, or later from the Options button on the Macro Window. You can use CTRL+ letter or CTRL+SHIFT+ letter, where letter is any letter key on the keyboard. The shortcut key will override any default Microsoft Excel shortcut keys while the workbook that contains the macro is open so take care not to use any letters that Excel uses like Ctrl+C for Copy. 4 . You can also add a short desription of your macro. Note:The shortcut key and description are optional.

Recording the Macro

Once you click the OK button the Macro Recorder will start. Remember every action that you make will be recorded. It’s a good idea to know exactly what you intend to do, maybe practice a few times before recording or even write some notes to follow. It is also to important to stop the recording when finished, Excel will continue recording your keystrokes until you stop it. It is not actually true to say Excel will record everything that you do. There are certain actions that the Recorder cannot replicate. It cannot capture actions that call any dialog boxes, like SaveAs. The Recorder cannot create functions, only sub routines. Code cannot be recorded within existing macros or subroutines. You can copy and paste into another macro after finishing recording. It cannot create any loops or repeating statements e.g If…Then, Do, While, etc It will not create any error handling or variables. It will take into account local settings or user options.

Record a macro that works dynamically on different ranges

Usually recorded macros will only work on the ranges actually used during the recording. However, there is a way to make this work dynamically. Perhaps you have a list of data that is vertically aligned and you need to convert it to horizontal. If you find yourself doing this regularly, then the Use Relative References button allows you to record a macro with actions that will always be relative to the initial cell selected. So if your list is in different positions, the recorded macro will always copy to the same range relative to first cell selected. All you need to do is launch the Record Macro dialog, but before recording click the Use Relative References button and follow the earlier instructions.

Drawbacks of the macro Recorder

The Macro recorder is a useful tool to help VBA novices get started. It is even useful when you become more proficient, it can be really handy to use the recorder to get the correct syntax for your code, such as for SaveAs or PasteSpecial. You can leave the code exactly as recorded and it will run fine every time you need it. However, much of the recorded code is unnecessary and may even make the code slower to run. Here’s some simple code that I recorded to copy a range from one sheet to another. When I started recording Sheet1 was active so the recorded code first selects Sheet3 where the range to be copied is. Next it selects C2:H11 before copying the contents. It then selects the destination sheet, Sheet1. Finally it selects A1 and pastes the selection there. It ends by clearing the ClipBoard. This code achieves what is needed by copying each step made when manually copying the range. However, as a general rule it is unnecessary to select Sheets or Ranges in Excel VBA and in fact the code above can be edited to perform much more efficiently by removing those stages. You may also see lines like this. ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.SmallScroll ToRight:=7 These lines only move the cursor and you can safely delete them. You can avoid many of these lines by planning your actions before recording. Don’t be put off by these drawbacks, the Macro recorder is an extremely useful tool for both novices and experienced users alike.
The Macro Recorder

Page Title

Making Excel work for you.
Excel macro recorder
Excel's Macro Recorder
Option Explicit Sub Macro1()' ' Macro1 Macro'       Sheets("Sheet3").Select     Range("C2:H11").Select     Selection.Copy     Sheets("Sheet1").Select     Range("A1").Select     ActiveSheet.Paste     Application.CutCopyMode = False End Sub  Sample Recorded Code
Option Explicit  Sub Macro1() ' ' Macro1 Macro'  ‘’/// one line to replace the recorded code     Sheets("Sheet3").Range("C2:H11").Copy Sheets("Sheet1").Range("A1").Paste ‘’/// clear th ClipBoard     Application.CutCopyMode = False End Sub  Edited Code

The Macro Recorder

Learn how to save time by recording a macro for repetitive tasks in Excel Start creating your own macros easily. Read this article now.
If you find this article useful You can help maintain my site by donating. Simply click the image below
Buy me a coffeeBuy me a coffee

© Ut duis incididunt ex officia

Veniam proident laboris. Deserunt voluptate cillum, laboris culpa nostrud ad, sunt quis. Labore ex minim occaecat ut. Velit in consectetur irure deserunt sit, ea do ut ea in mollit veniam tempor occaecat incididunt, esse id irure.
.

Recording a macro

You can use Excel's macros to automate tasks that you find yourself doing repeatedly. There are several ways to create macros, also called "subs" using Visual Basic for Applications (VBA), the programming language for Excel. You can copy some code from a friend or a Web site or you can write the VBA code yourself. A good way to get started is to record a simple macro using the Macro Recorder. Since the introduction of Excel 2007 you can open the Macro Recorder from the Developer Tab. There is also a button on the status bar that you can use to quickly record a macro which is just a shortcut to the "Record Macro" button on the Developer Tab. Once clicked it will change to a stop recording button, click it again to stop recording..

Using the macro Recorder

Using either method will display the Macro Recorder. 1 . The first step is to give your macro a descriptive name. The default name of your macro will be "Macro1", "Macro2", and so on depending on the number of macros that have already been recorded in that workbook. Macro names must begin with a letter and cannot resemble cell addresses (i.e. A1, D4, etc). You cannot use spaces (you can use the underscore character instead, e.g. My_Macro), dashes (hyphens) or other special characters in your macro name. If you try then you will get an error message - "That name is invalid". You can also include numbers in your macro names. 2 . Next decide where to store the macro. You can save it in the current workbook (ThisWorkbook). This choice will attach this macro to the current workbook, and it will only be accessible when this workbook is open. If you want to be able to use the macro from any workbook on that computer, choose the "Personal Macro Workbook". You can also decide to record to a new workbook. 3 . You have the option to add a keyboard shortcut to run your macro. Simply enter a letter in the Shortcut key box, either when you create the macro, or later from the Options button on the Macro Window. You can use CTRL+ letter or CTRL+SHIFT+ letter, where letter is any letter key on the keyboard. The shortcut key will override any default Microsoft Excel shortcut keys while the workbook that contains the macro is open so take care not to use any letters that Excel uses like Ctrl+C for Copy. 4 . You can also add a short desription of your macro. Note:The shortcut key and description are optional.

Recording the Macro

Once you click the OK button the Macro Recorder will start. Remember every action that you make will be recorded. It’s a good idea to know exactly what you intend to do, maybe practice a few times before recording or even write some notes to follow. It is also to important to stop the recording when finished, Excel will continue recording your keystrokes until you stop it. It is not actually true to say Excel will record everything that you do. There are certain actions that the Recorder cannot replicate. It cannot capture actions that call any dialog boxes, like SaveAs. The Recorder cannot create functions, only sub routines. Code cannot be recorded within existing macros or subroutines. You can copy and paste into another macro after finishing recording. It cannot create any loops or repeating statements e.g If…Then, Do, While, etc It will not create any error handling or variables. It will take into account local settings or user options.

Record a macro that works dynamically on different

ranges

Usually recorded macros will only work on the ranges actually used during the recording. However, there is a way to make this work dynamically. Perhaps you have a list of data that is vertically aligned and you need to convert it to horizontal. If you find yourself doing this regularly, then the Use Relative References button allows you to record a macro with actions that will always be relative to the initial cell selected. So if your list is in different positions, the recorded macro will always copy to the same range relative to first cell selected. All you need to do is launch the Record Macro dialog, but before recording click the Use Relative References button and follow the earlier instructions.

Drawbacks of the macro Recorder

The Macro recorder is a useful tool to help VBA novices get started. It is even useful when you become more proficient, it can be really handy to use the recorder to get the correct syntax for your code, such as for SaveAs or PasteSpecial. You can leave the code exactly as recorded and it will run fine every time you need it. However, much of the recorded code is unnecessary and may even make the code slower to run. Here’s some simple code that I recorded to copy a range from one sheet to another. When I started recording Sheet1 was active so the recorded code first selects Sheet3 where the range to be copied is. Next it selects C2:H11 before copying the contents. It then selects the destination sheet, Sheet1. Finally it selects A1 and pastes the selection there. It ends by clearing the ClipBoard. This code achieves what is needed by copying each step made when manually copying the range. However, as a general rule it is unnecessary to select Sheets or Ranges in Excel VBA and in fact the code above can be edited to perform much more efficiently by removing those stages. You may also see lines like this. ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.SmallScroll ToRight:=7 These lines only move the cursor and you can safely delete them. You can avoid many of these lines by planning your actions before recording. Don’t be put off by these drawbacks, the Macro recorder is an extremely useful tool for both novices and experienced users alike.
The Macro Recorder

Page Title

Logotype