Can Record Macro automatically use defined names instead of the cell names?

Question

I'm using record macro to do a simple copy and paste VBA code:

Range("F12").Select 
Selection.Copy 
Range("F10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

But I have defined the cells F12 and F10 (so if I am to add rows/columns it won't mess up the formula) and I'm wondering if there is a way to record the macro so that it would use the defined names automatically. Is this possible? Or is there a line I could add to the code after I record that would easily replace the cells with the defined names? Right now I am updating the code manually with the defined names. I would like to keep using record macro because I have do the copy and paste manually this time anyway, but I'm hoping to use VBA in the future.

Doubtful. But you should probably just use `Range("name1").Value = Range("name2").Value`. — BigBen, Jul 01 '21 at 19:15
... and try to [avoid using `Select` in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?r=SearchResults&s=1|223.8259) @Krissa — T.M., Jul 01 '21 at 19:16

score 0 · Answer 1 · answered Jul 06 '21 at 18:31

Depending upon volume of data/cells in reality (as opposed to in your Q which I assume may be 'scaled down' for practical reasons) you will find much quicker execution of VB by simply 'setting' the destination cells equal to the source values:

Sub Demo()

Application.ScreenUpdating = False
[named_cell_destination].Value = [named_cell_source].Value
Application.ScreenUpdating = False

End Sub

where: 'named_cell_source' and 'named_cell_destination' are names you have ascribed from within excel itself.

Alternatively, if you wanted to ascribe / add named ranges from within excel, and adopt a similar approach as above thereafter, you could do something like this:

Sub Demo2()

Dim Rng, Rng2 As Range



  Set Rng = Range("A1:B8")
  Set Rng2 = Range("A12:B19")
  
  ThisWorkbook.Names.Add Name:="Range_1", RefersTo:=Rng
  ThisWorkbook.Names.Add Name:="Range_2", RefersTo:=Rng2
  
  [Range_2].Value = [Range_1].Value

End Sub

see wallstreetmojo for more info in this regard.

Can Record Macro automatically use defined names instead of the cell names?

1 Answers1