In Excel VBA, get a worksheet by name with Set ws = ThisWorkbook.Worksheets("Summary"), replacing Summary with the tab name. Use ThisWorkbook when the sheet is in the workbook containing your code; use a specific workbook reference when it is elsewhere.
The basic worksheet lookup
A worksheet variable lets your macro refer to a particular tab without repeatedly selecting it. Declare the variable as a Worksheet, then use Set to assign the worksheet object. For example:
Sub ReadSummary()
Dim ws As Worksheet
' Get the Summary worksheet in the workbook containing this code.
Set ws = ThisWorkbook.Worksheets("Summary")
MsgBox ws.Range("A1").Value
End Sub
The name inside quotation marks must match the worksheet tab name. It is the visible tab name, not the worksheet’s VBA code name. Once the variable is assigned, use it to refer to cells and other worksheet properties, such as ws.Range("A1"), ws.Cells(2, 3), or ws.Visible.
In VBA, assigning an object requires Set. Writing ws = ThisWorkbook.Worksheets("Summary") instead will not correctly assign the worksheet object. Also, Worksheets is a collection, and the parentheses provide the key used to find an item in that collection.
Choose the workbook reference deliberately
The workbook reference is just as important as the sheet name. ThisWorkbook means the workbook in which the running VBA code is stored. This is usually the clearest choice for code kept in a workbook’s standard module, worksheet module, or ThisWorkbook module.
ActiveWorkbook means the workbook currently active in Excel. That might be a different file, especially if a macro opens another workbook or the user switches windows. A macro that searches ActiveWorkbook can therefore find the wrong sheet or fail even though the expected sheet exists in the macro file.
For a workbook you have explicitly opened or assigned, use its object variable. This makes the target clear and avoids relying on which workbook happens to be active:
Sub GetSheetFromTargetWorkbook()
Dim targetBook As Workbook
Dim ws As Worksheet
' Set targetBook to the workbook your code intends to use.
Set targetBook = ThisWorkbook
Set ws = targetBook.Worksheets("Summary")
MsgBox ws.Name
End Sub
This sample sets the target to ThisWorkbook simply to show the pattern; replace that assignment if your macro has already opened or received another workbook. Do not assume that a workbook’s filename, window caption, or active status automatically identifies the correct worksheet container.
Use the worksheet without selecting it
You generally do not need to activate or select a worksheet before reading or changing it. Direct object references are clearer and avoid changing the user’s current selection. For example, if the Summary tab is in the workbook containing the code, this writes a value directly:
Sub UpdateSummary()
Dim ws As Worksheet
' Save a copy of the workbook before running a macro that changes data.
Set ws = ThisWorkbook.Worksheets("Summary")
ws.Range("B2").Value = "Complete"
End Sub
The comment is a useful precaution: VBA changes cannot be reversed with Excel’s Ctrl+Z undo command. If code genuinely needs a sheet to be active, the object can be activated with ws.Activate, but that should be a deliberate user-interface step, not a prerequisite for ordinary cell work.
Handle a worksheet that might be missing
If the requested name is not present in the specified workbook, the direct lookup raises run-time error 9, “Subscript out of range.” This commonly happens when a tab has been renamed, a trailing space was included in the name, or the code searched the wrong workbook. When absence is possible, check for it instead of allowing the macro to stop unexpectedly.
Sub CheckForSummary()
Dim ws As Worksheet
' Save a copy before running any macro that changes workbook contents.
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Summary")
On Error GoTo 0
If ws Is Nothing Then
MsgBox "The Summary worksheet was not found."
Exit Sub
End If
MsgBox "Found: " & ws.Name
End Sub
On Error Resume Next lets VBA continue past the failed lookup, while On Error GoTo 0 immediately turns that temporary error suppression off. Keeping the suppressed-error region short matters: if it remains active for the rest of a procedure, unrelated problems can be hidden. The Nothing test tells you whether the object variable received a worksheet.
For code that should create the tab when it is missing, do that only after deciding what the workbook is meant to contain. A typo should not silently produce a new, blank tab in place of a correctly named existing one. A clear message or a controlled creation step is safer than treating every lookup failure as permission to add a worksheet.
Make the existence check reusable
If several procedures need to test for a worksheet, put the lookup in a small function. Passing the workbook as an argument makes the function usable with different workbooks and makes its scope explicit:
Function WorksheetExists(ByVal wb As Workbook, ByVal sheetName As String) As Boolean
Dim ws As Worksheet
' A failed lookup leaves ws as Nothing.
On Error Resume Next
Set ws = wb.Worksheets(sheetName)
On Error GoTo 0
WorksheetExists = Not ws Is Nothing
End Function
For example, a calling procedure can use If WorksheetExists(ThisWorkbook, "Summary") Then before attempting a lookup. The function returns True when that workbook has a worksheet with the requested name and False when it does not. Because the parameter is declared as Workbook, pass a workbook object, not a filename string.
This function checks only worksheets. It does not report a chart sheet as a worksheet, which is often the desired behavior. If a procedure needs to work with any sheet tab, including chart sheets, choose a broader sheet-object approach and account for the fact that a chart sheet does not have worksheet cells.
Worksheets versus Sheets
ThisWorkbook.Worksheets("Summary") searches the workbook’s worksheet collection. ThisWorkbook.Sheets("Summary") searches its sheets collection, which can include both worksheets and chart sheets. Use Worksheets when the code expects cells, ranges, rows, or columns; its type states that expectation and avoids returning a chart sheet where a worksheet is required.
Use Sheets only when the code is intentionally handling different kinds of tabs. A variable declared As Worksheet cannot hold a chart sheet. A variable declared As Object can accommodate different sheet object types, but then the code must check what it received before calling worksheet-only members such as Range. For routine cell automation, the more specific Worksheet type is clearer and safer.
Names, indexes, and common lookup traps
A worksheet can also be retrieved by its position, for example Worksheets(1), but an index is not a stable substitute for a name when tabs might be reordered. If a macro’s purpose is to find the tab called Summary, name-based lookup communicates that intent and continues to identify that tab after someone moves it.
- Use the tab caption: the string in
Worksheets("Summary")is the displayed worksheet name, not a cell value or the workbook filename. - Check for extra spaces:
"Summary "is not the same text as"Summary". Copying the visible name or inspecting the tab can help find a mismatch. - Remember the workbook scope: a matching tab in another open workbook does not satisfy a lookup against
ThisWorkbook. - Do not confuse code name and tab name: the VBA editor’s project pane may show a code name as well as the tab caption.
Worksheets("Summary")looks up the caption.
Excel treats worksheet names as case-insensitive for ordinary lookup, so a capitalization difference alone is not usually the cause of error 9. Names are limited to 31 characters and cannot include certain characters, including a colon, backslash, slash, question mark, asterisk, or square brackets. Those restrictions mostly matter when a macro creates or renames tabs, but they can explain why a desired name was not accepted in the first place.
Run VBA in desktop Excel and protect the file
In Excel for Microsoft 365 on Windows, open the VBA editor from Developer > Visual Basic. If the Developer tab is not shown, enable it through File > Options > Customize Ribbon. You can place a procedure in a standard module in the editor. Excel for the web does not run VBA macros; use desktop Excel to create or run them. Excel for Mac supports VBA in the desktop app, although editor access and some interface details differ from Windows.
Before running code that edits a workbook, save a separate copy. VBA actions cannot be undone with Ctrl+Z, so a mistaken write or deletion may require restoring a saved version. If the macro needs to be retained in the workbook, save it in a macro-enabled format such as .xlsm; a standard .xlsx workbook does not store VBA project code.
Only enable or run macros from files you trust. A macro can change or delete workbook contents, and VBA can perform actions beyond the sheet lookup shown here. A worksheet-by-name statement is not itself a security check; the safety question is whether the macro and its source are trustworthy and whether you understand the changes it will make.
Frequently Asked Questions
How do I get a worksheet by name in VBA?
Declare a worksheet variable and assign it with Set ws = ThisWorkbook.Worksheets("SheetName"). Replace SheetName with the actual tab name and use the correct workbook reference.
What does error 9 mean when I use Worksheets by name?
Error 9, “Subscript out of range,” usually means the specified worksheet name is not in the workbook object you searched. Check spelling, spaces, and whether you meant ThisWorkbook, ActiveWorkbook, or another workbook.
Can I use the worksheet’s code name instead of its tab name?
Yes, but that is a different reference style. A worksheet code name can be used directly in VBA within its project, while Worksheets("Summary") uses the tab caption. The code name does not change when the tab is renamed, but it is not a string lookup and is specific to that workbook’s VBA project.
Should I use Sheets or Worksheets?
Use Worksheets when you need a normal cell-based worksheet. Use Sheets when you intentionally need to include other sheet types, such as chart sheets, and your code can handle those types.
Can VBA get a sheet by name in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not execute VBA. Run the macro in the desktop version of Excel instead.
Related
- Excel Split Cell Contents by Delimiter
- Excel Split Cell Based on Character: Methods That Work
- Excel Split Cell at a Specific Character?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Range Find Exact Match with xlWhole ExplainedTo find a cell whose entire contents match a value in Excel VBA, call Range.Find with LookAt:=xlWhole, and…
- Excel VBA Function Syntax: Examples and RulesExcel VBA function syntax starts with a Function statement and ends with End Function; the function returns a…
- Excel VBA Find Sheet by Name: Safe Code and ExamplesTo find a worksheet by its tab name in Excel VBA, use ThisWorkbook.Worksheets("Summary") and handle the error that…
- Excel VBA: Bring a Workbook Window to the Front ReliablyTo bring an open workbook to the front with Excel VBA, activate Excel and then activate the workbook:…
- Excel VBA InputBox Password: Masking and Secure OptionsExcel’s built-in VBA InputBox cannot hide a password as the user types; it displays the entered characters. To…
- Excel VBA Erase Array: What It Does and When to Use ItIn Excel VBA, use Erase arrayName to reset an array: it releases the allocated storage of a dynamic…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.