In this article, we are going to learn how to record a macro, and then how we can modify it in very simple coding.
When we record any macro, then Excel creates own coding as per our steps which we do at the time of recording. For better understanding, let’s take an example. We want to type week names in the range B1:H1 from Sunday to Saturday. Firstly, we need to record a macro. To record the macro, follow below steps: –
- Open the Excel sheet
- Go to Developer tab > Code > Click on Record Macro
- You will get Record Macro’s dialog box
- In which you have to give the Macro Name
- Then Short cut key, which you can use to run the Macro
- If you want, then you can give the description as well
- Click on OK, and recording will get start
- Enter the Sunday in cell B1, Monday in cell C1, Tuesday in cell D1 …………….. Saturday in cell H1
- Stop the recording:- Developer Tab > Code > Stop Recording
- To see the recorded coding :- Developer Tab > Code > Macros
- You will get Macro dialog box. Click on Edit to see the recorded code
- To see the recorded coding:- Developer Tab > Code > Macros
- VBE will get open and then you can see the recorded Macros
Now you can see that for a very small thing, Excel generated very lengthy code. We needs to modify the coding and to modify the coding, we will use With-End with construct.
Note:- While using this Code, whatever your Active cell, the macro will fill the weeks name from the active cell to next cells.
We can more modify the same code just see the below code:-