Is there a way to streamline naming hundreds of cells?

Question

I have two sheets "Opponent 1" and "Opponent 2", with hundreds of cells that need to be renamed. All the cells will stay the same except for one aspect, an identifying character.

Each cell should have its original name + "OP1_" or "OP2_", depending on which sheet it is in.

Example:

• A1 changes to OP1_A1
• A2 changes to OP1_A2

I have tried multiple things:

• Creating macros

  • Some with keyboard shortcuts.

    Sub NameChange()
    '
    ' NameChange Macro
    '
    ' Keyboard Shortcut: Ctrl+n
    '
        ActiveCell.Select
        ActiveWorkbook.Names.Add Name:="OP1_", RefersToR1C1:="='Opponent 1'!R1C1"
    End Sub
    

  • Some that I copy/past the recorded macro and change the name

    <sub>    
    ActiveWorkbook.Names.Add Name:="OP1_A1", RefersToR1C1:="='Opponent 1'!R1C1"
        ActiveWorkbook.Names.Add Name:="OP1_A2", RefersToR1C1:="='Opponent 1'!R1C1"
        ActiveWorkbook.Names.Add Name:="OP1_A3", RefersToR1C1:="='Opponent 1'!R1C1"
    </sub>
    

  • As well as just going through each individual cell to change the name

I keep seeing if/then, for loop, and iteration suggestions, but I don't know anything about coding.

All I know is pseudo-code,

For example:

        If Cell location is in sheet "Opponent 1",
            Then add "OP1_" to beginning of Cell name
        If Cell location is in sheet "Opponent 2",
            Then add "OP2_" to beginning of Cell name

This is the only thing I can think of to handle what I am trying to do. But again, it is just pseudo-code.

Answer 1

I'm assuming the ranges on the two worksheets might be different.
ThisWorkbook is a reference to the workbook containing the code while ActiveWorkbook in your code is a reference to whichever workbook is currently active.

The code is added to a normal module (select Insert > Module).

Sub NameChange()

    With ThisWorkbook

        Dim OP1 As Range
        Set OP1 = .Worksheets("Opponent 1").Range("A1:D10") 'Update to the correct range.
        
        Dim OP2 As Range
        Set OP2 = .Worksheets("Opponent 2").Range("A1:C5") 'Update to the correct range.
        
        Dim Cell As Range
        For Each Cell In OP1
            ThisWorkbook.Names.Add "OP1_" & Cell.Address(False, False), RefersTo:=Cell
        Next Cell
        
        For Each Cell In OP2
            ThisWorkbook.Names.Add "OP2_" & Cell.Address(False, False), RefersTo:=Cell
        Next Cell
    
    End With

End Sub