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
- Excel Text to Columns: What the Text Qualifier Does
- Excel Text to Columns: Split at the First Delimiter
- Excel Text to Columns: Use a Line Break Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Length of String: How Len WorksIn VBA, use the Len function to get the length of a string: Len(text) returns a Long count…
- Excel VBA Cell Text Color: Set, Read, and ResetIn Excel VBA, set a cell’s text color with its Font.Color property, usually assigning an RGB value: Range("B2").Font.Color…
- Excel VBA Split: Separate Text with the Split FunctionExcel VBA’s Split function separates a string wherever it finds a specified delimiter and returns the resulting pieces…
- VBA Find After: How Excel Chooses the Search Start CellIn Excel VBA, the After argument in Range.Find tells Excel which single cell to search after when beginning…
- Excel VBA: Check Whether a Cell Contains TextTo check whether a cell contains a particular string in Excel VBA, use InStr and test whether its…
- Is Excel Macro Recorder Safe to Use? Risks and LimitsExcel’s Macro Recorder is generally safe to use for recording your own routine actions, but it does not…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.