How to Record a Macro and Modify it

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

Image 1

  • You will get Record Macro’s dialog box

image 2

  • 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

image 3

  • 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

image 4

  • You will get Macro dialog box. Click on Edit to see the recorded code
  • To see the recorded coding:- Developer Tab > Code > Macros

image 5

  • 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.

Sub Week_Name()
‘ Week_Name Macro
‘ Week Name
‘ Keyboard Shortcut: Ctrl+Shift+W
With ActiveCell
.Offset(0, 0) = “Sunday”
.Offset(0, 1) = “Monday”
.Offset(0, 2) = “Tuesday”
.Offset(0, 3) = “Wednesday”
.Offset(0, 4) = “Thursday”
.Offset(0, 5) = “Friday”
.Offset(0, 6) = “Saturday”
End With
End Sub

image 6

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:-

Sub Week_Name()
‘ Week_Name Macro
‘ Week Name
‘ Keyboard Shortcut: Ctrl+Shift+W
ActiveCell.Resize(, 7) = Array(“Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”)
End Sub

image 7

 

Previous Chapter Next Chapter

Leave a Reply