How to modify the generated code by a Record New Macro

If you have been using Excel for a while, you may have seen a menu item called Record New Macro in the Tools > Macro menu. The Record New Macro function is one of the most convenient features of Excel as it automatically generates VBA code according to operations performed with Excel. Of course, you can use the generated code without modification if exactly the same operation is performed each time. But it does become necessary to modify the code in cases such as when the source data range is changed or the operation must be repeated a number of times.

For instance, the following code has been recorded when copying range A1:A10 and pasting to range B1:B10.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2005/8/24 by Masaru Kaji aka Colo '

'
Range("A1:A10").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
End Sub

To change the ranges, you can easily change the source range and the destinations just by rewriting the recorded cell address in the code, such as replacing A1:A10 with C1:C10 in the above code as an example.

Now, let's imagine you would like to apply this procedure to ten worksheets in a workbook. All of the required code can be generated by using Record New Macro. In this case, the generated code will be as follows.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2005/8/24 by Masaru Kaji aka Colo '

'
Sheets("Sheet1").Select
Range("A1:A10").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("A1:A10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("A1:A10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
Range("A1:A10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet5").Select
Range("A1:A10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet6").Select
Range("A1:A10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet7").Select
Range("A1:A10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet8").Select
Range("A1:A10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet9").Select
Range("A1:A10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet10").Select
Range("A1:A10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
End Sub

As you can see, it's quite a lot of code. But if you look closely, you might notice that some of this code is repeated. That's right, there are no differences with the operations performed with each worksheet, the code where the worksheets themselves are selected is the only part that changes. This enables us to greatly simplify the code by using what is referred to as a Loop.

First of all, prepare a variable that relates to the worksheet object. If you're not familiar with the word Variable, just think of it as something that acts as a substitute. For example, if you write Set sh = Sheets("Sheet1"), the variable "sh" refers to Sheet1.

Here, the variable named "sh" is prepared as a variable to represent the worksheet object.

In the next example, all of the repeating code has been replaced with a For Each - Next syntax, to loop through all of the worksheets in the active workbook.

Sub Macro3()

'
' Macro2 Macro
' Macro recorded 2005/8/24 by Masaru Kaji aka Colo '

'

'This is a variable for the Worksheet object
Dim sh As Worksheet

'Using a loop, you can apply the same code to all worksheets
For Each sh In Worksheets
'Select the target worksheet
sh.Select

'In the following part, the recorded code is used as is.
Range("A1:A10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Next
End Sub

You can see the code has become a lot shorter! And it is also a lot easier to modify if you just want to change the source range.

The thing to remember is to not just take the code generated by Record New Macro for granted, there may be unnecessary expressions included that will slow the code execution speed down.