Excel VBA Dynamic Worksheet Names: How They Work

VBA & macros · Updated November 21, 2026

In Excel VBA, make a worksheet name dynamic by storing the desired tab name in a string variable and using that variable with the Worksheets collection—for example, Worksheets(sheetName). You can get the name from a cell, a date, or another value, then use it to find, rename, or create a worksheet.

What “dynamic worksheet name” means in VBA

The phrase can describe two different tasks: selecting a worksheet whose name may vary, or changing a worksheet’s tab name while a macro runs. Both rely on the worksheet’s Name property and Excel’s Worksheets collection, but they are not the same operation. Referencing a sheet does not rename it; changing its name requires assigning a new value to its Name property.

A fixed reference such as Worksheets("January") works only while that tab is still named January. A variable reference, such as Worksheets(sheetName), lets the code choose a different tab at runtime. That is useful for monthly reports, department workbooks, and files where a user selects a tab name on a control sheet.

In these examples, ThisWorkbook means the workbook containing the VBA code. It is usually safer than ActiveWorkbook, which means whichever workbook currently has focus and could change if another file is opened or activated.

Get a worksheet name from a cell

Suppose a worksheet called Control has the requested tab name in cell B2. The macro below reads that value, checks whether a sheet with that name exists in the workbook containing the code, and then uses the worksheet. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.

Sub UseSheetNamedInCell()
    Dim sheetName As String
    Dim ws As Worksheet

    ' Read the requested worksheet name from the Control sheet.
    sheetName = Trim$(CStr(ThisWorkbook.Worksheets("Control").Range("B2").Value))

    If Len(sheetName) = 0 Then
        MsgBox "Enter a worksheet name in Control!B2."
        Exit Sub
    End If

    ' Look up the tab; handle a missing worksheet without stopping the macro.
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "No worksheet named " & sheetName & " was found."
        Exit Sub
    End If

    ws.Range("A1").Value = "Selected worksheet: " & ws.Name
End Sub

The final line writes to A1 on the selected sheet. If the tab cannot be found, the macro displays a message instead. The brief On Error Resume Next is limited to the lookup, then disabled with On Error GoTo 0; leaving error suppression active across a whole procedure can conceal unrelated problems.

Build a name from a date or other value

A macro can construct a tab name instead of reading a complete name from a cell. For example, if Control!B3 contains a valid Excel date, Format$ can turn it into a month-and-year label. The format string controls the displayed name; "mmm yyyy" produces labels such as “Mar 2026.” The code below looks for that existing tab and reports if it is missing.

Sub FindMonthlySheet()
    Dim reportDate As Date
    Dim sheetName As String
    Dim ws As Worksheet

    ' Control!B3 must contain a valid date.
    reportDate = ThisWorkbook.Worksheets("Control").Range("B3").Value
    sheetName = Format$(reportDate, "mmm yyyy")

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "The worksheet " & sheetName & " does not exist."
        Exit Sub
    End If

    ws.Activate
End Sub

Formatting can affect whether the generated string matches the tab exactly: “March 2026” and “Mar 2026” are different names. The value must also be a valid date before assigning it to a Date variable. For more robust workbooks, validate the cell first and decide what the macro should do when that month’s sheet has not yet been created.

Rename a worksheet dynamically

To change a tab’s name, set its Name property. This example renames Sheet1 using the value in Control!B2 and reports an error if Excel rejects the new name. It assumes both source sheets exist.

Sub RenameSheetFromCell()
    Dim ws As Worksheet
    Dim newName As String

    ' Read the requested name and identify the worksheet to rename.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    newName = Trim$(CStr(ThisWorkbook.Worksheets("Control").Range("B2").Value))

    If Len(newName) = 0 Then
        MsgBox "The new worksheet name cannot be blank."
        Exit Sub
    End If

    On Error GoTo RenameFailed
    ws.Name = newName
    MsgBox "Worksheet renamed to " & ws.Name
    Exit Sub

RenameFailed:
    MsgBox "Excel could not use that worksheet name. Check its length, characters, and whether it is already in use."
End Sub

Excel does not allow a blank worksheet name, a name longer than 31 characters, or these characters in a tab name: colon, backslash, forward slash, question mark, asterisk, and square brackets. A name also has to be unique within the workbook. Trimming surrounding spaces is helpful, but it does not replace validation; a name containing a forbidden character will still fail.

Create a sheet when the name is not found

Sometimes the intended behavior is to reuse a worksheet if it exists and add one if it does not. The following procedure searches the workbook by name, then adds a worksheet at the end only when there is no match. The proposed name must still meet Excel’s naming rules.

Sub GetOrCreateSheet()
    Dim sheetName As String
    Dim ws As Worksheet
    Dim candidate As Worksheet

    ' Read the name to find or create.
    sheetName = Trim$(CStr(ThisWorkbook.Worksheets("Control").Range("B2").Value))
    If Len(sheetName) = 0 Then Exit Sub

    ' Search existing worksheet tabs.
    For Each candidate In ThisWorkbook.Worksheets
        If StrComp(candidate.Name, sheetName, vbTextCompare) = 0 Then
            Set ws = candidate
            Exit For
        End If
    Next candidate

    ' Add a tab only if the search found no match.
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add( _
            After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = sheetName
    End If

    ws.Range("A1").Value = "Ready"
End Sub

The comparison uses vbTextCompare, so the search treats upper- and lowercase letters as equivalent. Excel worksheet names are not distinguished by letter case for uniqueness, so trying to create another tab that differs only by capitalization will not make a separate worksheet. The line that adds the sheet uses ThisWorkbook throughout to avoid accidentally adding a tab to a different active file.

Choose the right workbook and collection

Use Worksheets when you mean ordinary worksheet tabs. A workbook’s broader Sheets collection can also include chart sheets, so it is less specific. When the macro is stored in the workbook it should edit, ThisWorkbook.Worksheets(sheetName) is generally clear and dependable.

Use ActiveWorkbook.Worksheets(sheetName) only when the macro is deliberately meant to act on the workbook the user currently has active. This distinction matters in add-ins, personal macro workbooks, and automation that opens several files. A common failure is to qualify the worksheet with the wrong workbook, which produces a “subscript out of range” error even though a tab with that name is visible elsewhere.

Common errors and practical checks

  • “Subscript out of range”: The name does not match a worksheet in the workbook collection being searched. Check spelling, leading or trailing spaces, and whether the code uses the intended workbook.
  • Invalid name when renaming or adding: Check the 31-character limit, prohibited characters, blank values, and duplicates before assigning the name.
  • A cell value looks like the right name but is not: A cell may contain a date, an error value, or spaces. Convert and validate the value deliberately rather than assuming it is already a clean string.
  • The macro selects the wrong file: Replace unqualified references such as Worksheets(sheetName) with a workbook-qualified reference when the target is known.
  • The code cannot run: Excel for the web does not run VBA macros. Open the workbook in the desktop Excel app. On Windows, Alt+F11 opens the Visual Basic Editor; on Mac, Option+F11 is commonly used, though some keyboards require Fn as well.

For a workbook that contains important data, test a macro on a copy first. Save macro-enabled workbooks as .xlsm if you need to retain VBA code. Only enable macros in files from sources you trust.

Dynamic VBA names versus formulas

A VBA variable is useful while a macro is running, but it is not automatically a worksheet formula reference that recalculates whenever a cell changes. If a user edits Control!B2, VBA does not rerun merely because that value changed. You must run the macro again, call it from another procedure, or deliberately use a worksheet event such as a change event. Event-driven code needs extra care to avoid repeated events or unintended edits.

Likewise, changing a tab’s name is different from creating a dynamic reference in a cell formula. If the goal is simply to select a sheet in a macro, use a worksheet object variable. If the goal is to update or create tabs as part of a workflow, add explicit checks for existing sheets and valid names. Choosing the operation first makes the code easier to understand and troubleshoot.

Frequently Asked Questions

How do I refer to a worksheet by a name stored in a cell?
Read the cell into a String variable, then use ThisWorkbook.Worksheets(sheetName). Check that the value is not blank and handle the case where no worksheet with that name exists.

Can VBA create a worksheet name from today’s date?
Yes. Convert a date to text with VBA’s Format$ function, then use the result to find, add, or rename a sheet. Make sure the chosen date format produces a valid name and matches your naming convention.

Why does “subscript out of range” appear?
Usually, the requested name is not present in the workbook collection being referenced. The tab may be misspelled, the cell value may contain extra spaces, or the code may be looking in a different workbook.

Can a worksheet tab name contain spaces?
Yes. Spaces are allowed, and VBA can use a name such as “Quarter 1” in a string variable. Avoid Excel’s prohibited characters and keep the name within the 31-character limit.

Can Excel for the web run a VBA macro?
No. Excel for the web does not run VBA. Open the workbook in desktop Excel for Windows or Mac to use its VBA code.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.