In Excel VBA, link to a worksheet by referring to it with ThisWorkbook.Worksheets("SheetName"); to create a clickable link that takes a user to a sheet, use the worksheet’s Hyperlinks.Add method. These are different tasks: a worksheet reference lets code work with a sheet, while a hyperlink lets someone navigate to it.
What “link to a worksheet” can mean
The phrase can describe either a programming reference or a navigation link. If your macro needs to read a value, write data, or format cells on another sheet, it needs a worksheet object reference. The reference tells VBA which worksheet object to work with. It does not, by itself, create a visible link in the workbook.
If you want a person to click a cell and jump to another sheet, you need an internal hyperlink. VBA can add that hyperlink to a cell, and the link appears in the worksheet like other clickable links. You can also create a link without VBA using Excel’s Insert > Link command, then choose a place in the document.
There is a third, related case: a formula or VBA expression can retrieve a value from another worksheet. That is still a reference to a sheet, not a navigation link. Keeping these meanings separate helps you choose the right method and avoid writing a macro that changes the active sheet when all it needed was to read a cell.
Refer to a worksheet by name
For a sheet in the workbook containing the macro, use ThisWorkbook.Worksheets("Data"), replacing Data with the worksheet’s tab name. Assign the reference to a variable declared as a Worksheet, then qualify the ranges and cells you use. This makes the target clear and avoids relying on whichever sheet happens to be active.
Sub ReadFromDataSheet()
Dim sourceSheet As Worksheet
Dim lastRow As Long
' Point to the Data sheet in the workbook containing this code.
Set sourceSheet = ThisWorkbook.Worksheets("Data")
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
MsgBox "A1 contains: " & sourceSheet.Range("A1").Value & _
"; last used row in column A: " & lastRow
End Sub
The example reads cell A1 and finds the last nonempty cell in column A. Rows.Count is taken from the referenced sheet, so the expression does not depend on the active worksheet. The variable sourceSheet is an object variable, which is why VBA assigns it with Set.
Worksheet names must match the tab name, including spaces and punctuation. The name inside the VBA string is not surrounded by the apostrophes sometimes used in worksheet formulas. For example, a tab named Monthly Report is referenced as ThisWorkbook.Worksheets("Monthly Report").
Choose the right workbook and worksheet collection
ThisWorkbook means the workbook where the VBA code is stored. ActiveWorkbook means the workbook currently active in Excel. They can be different: a macro stored in a personal macro workbook or an add-in can run while another workbook is active. For a macro intended to operate on its own file, ThisWorkbook is usually the clearer choice.
Use Worksheets when the target is an ordinary worksheet. Excel also has chart sheets, which are not worksheets; the broader Sheets collection can include both. A variable declared As Worksheet should be assigned from Worksheets, not from a chart sheet.
For a different workbook, first make sure that workbook is open, then identify it explicitly. For example, Workbooks("Source.xlsx").Worksheets("Data") refers to the Data worksheet in an open workbook named Source.xlsx. If the workbook is not open, VBA cannot find it through the Workbooks collection; opening the file is a separate step. Avoid depending on a workbook’s position in the collection, because opening or closing files can change that position.
Read and write without selecting the sheet
A worksheet reference does not require Select or Activate. In most macros, directly qualify the range instead. This is faster to understand, avoids unnecessary screen changes, and is less likely to fail when a different workbook or sheet is active.
Sub CopyValueToSummary()
Dim sourceSheet As Worksheet
Dim summarySheet As Worksheet
' Set both worksheet references explicitly.
Set sourceSheet = ThisWorkbook.Worksheets("Data")
Set summarySheet = ThisWorkbook.Worksheets("Summary")
summarySheet.Range("B2").Value = sourceSheet.Range("A2").Value
End Sub
This copies the value in Data!A2 to Summary!B2; it does not copy formatting or create a live formula. To transfer a larger block, assign matching ranges, such as a source range to a destination range, after ensuring their dimensions are compatible. If you need formulas or formatting preserved, choose an appropriate copy method rather than assuming that assigning .Value transfers everything.
Activate a sheet only when navigation is required
Sometimes the macro’s purpose is to show a particular worksheet to the user. In that case, activate it explicitly:
Sub ShowSummarySheet()
Dim targetSheet As Worksheet
' Activate the Summary sheet in this workbook.
Set targetSheet = ThisWorkbook.Worksheets("Summary")
targetSheet.Activate
End Sub
Activation changes the user’s view, but it is not needed to read or write cells. A hidden worksheet cannot be activated until it is made visible. Also, a macro that selects a sheet can produce confusing results if the workbook containing the target is not the workbook the user expects. Use an explicit workbook reference and reserve activation for an intentional navigation step.
Create a clickable internal hyperlink with VBA
To add a link in a cell, call Hyperlinks.Add on the worksheet that will contain the link. Set Address to an empty string for a link within the current workbook, and set SubAddress to the destination sheet and cell. This example places a clickable link in Dashboard!A1 that opens Monthly Report!A1.
Sub AddMonthlyReportLink()
Dim dashboardSheet As Worksheet
' Add an internal link on the Dashboard sheet.
Set dashboardSheet = ThisWorkbook.Worksheets("Dashboard")
dashboardSheet.Hyperlinks.Add _
Anchor:=dashboardSheet.Range("A1"), _
Address:="", _
SubAddress:="'Monthly Report'!A1", _
TextToDisplay:="Open monthly report"
End Sub
The Anchor is the cell where the hyperlink appears. The SubAddress names the destination, and the single quotes around Monthly Report make the sheet name unambiguous because it contains a space. Change the sheet and cell in that string to set another destination. If the anchor cell already contains a hyperlink, remove or replace the existing link as part of your workflow so repeated runs do not leave confusing content.
For a link to a website or an external file, provide a suitable path or URL in Address; that is a different kind of hyperlink from an in-workbook link. A broken external path can result if the file moves, while an internal sheet link is tied to a sheet in the workbook. If you only want to navigate and do not need a clickable cell, activating the destination sheet is simpler.
Common errors and how to diagnose them
- Subscript out of range: VBA cannot find the named workbook or worksheet in the collection being referenced. Check spelling, spaces, workbook name, and whether the target file is open.
- Object variable not set: A worksheet variable was declared but not assigned with
Setbefore use. Confirm the assignment runs successfully before using the variable. - Wrong sheet receives the change: An unqualified
Range("A1")orCells(...)can refer to the active sheet. Qualify it with the worksheet variable, such assourceSheet.Range("A1"). - Cannot activate the sheet: The worksheet may be hidden, or the workbook reference may not point to the workbook you intend. Check visibility and workbook qualification rather than repeatedly calling
Activate. - Hyperlink goes nowhere: Inspect the exact sheet name and cell in
SubAddress. The destination must exist in the workbook, and a link to an external workbook requires a valid address or path.
When debugging, step through the macro in the Visual Basic Editor and inspect the worksheet variable and workbook names. On Windows, Alt+F11 opens the editor; on Mac, Option+F11 may require the Fn key depending on the keyboard settings. The Developer tab’s Visual Basic button is another route. A precise error message is usually more useful than adding On Error Resume Next, which can hide the actual cause.
Save, run, and maintain VBA safely
Before running a macro that changes a workbook, save a separate copy. VBA macros cannot be undone with Ctrl+Z, so a mistaken write, deletion, or formatting change may not be reversible through Excel’s undo history. Test changes on a copy, especially when the macro loops through many sheets or cells.
Save a workbook containing VBA in a macro-enabled format such as .xlsm; saving it as a standard .xlsx file removes its VBA project. On Windows, Excel for Microsoft 365 includes the Visual Basic Editor for writing and running VBA. Excel for the web can open and edit many workbook contents, but it cannot create, run, or edit VBA macros; open the file in the desktop application to use the macro. Excel for Mac supports VBA, though some Windows-specific automation and interfaces may not be available or behave identically.
Macros may also be blocked by Trust Center settings or organizational security policies. Enable or run code only when you understand its source and purpose. A workbook reference such as ThisWorkbook.Worksheets("Data") does not bypass macro security; it only identifies an object after the code is allowed to run.
Frequently Asked Questions
How do I reference another worksheet in Excel VBA?
Declare a Worksheet variable, then assign it with Set ws = ThisWorkbook.Worksheets("SheetName"). Replace SheetName with the exact tab name, and qualify ranges with ws when reading or changing cells.
How do I link to a worksheet in the same workbook?
For code access, use a worksheet object reference. For a clickable cell, use Hyperlinks.Add with an empty Address and a SubAddress such as 'Summary'!A1. The first is for VBA operations; the second is for user navigation.
What is the difference between ThisWorkbook and ActiveWorkbook?ThisWorkbook is the file containing the running VBA code. ActiveWorkbook is the file currently in focus in Excel. They may differ, so use the one that matches the workbook your macro should control.
Do I need to activate a worksheet before changing a cell?
No. Refer directly to the cell through a worksheet variable, for example ws.Range("B2").Value = "Done". Activate a sheet only when you want to display it to the user.
Can Excel for the web run VBA macros?
No. Excel for the web does not run VBA. Use the Excel desktop app for Microsoft 365 on Windows or a supported desktop version on Mac, and save the workbook in a macro-enabled format to retain its VBA code.
Related
- Excel Text to Columns: Find It and Split Data Correctly
- Excel Text to Columns: Split Cell Text at Line Breaks
- Excel Text to Columns: Advanced Options and Uses
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find: Check Whether a String Contains a SubstringIn VBA, use InStr to check whether one string contains another: if the function returns a number greater…
- VBA Macro to Compare Two Excel SheetsA VBA macro can compare two Excel worksheets cell by cell and list mismatches on a separate report…
- Count Cells by Color in Excel VBA: A Practical GuideTo count cells by fill color in Excel VBA, run a macro that compares each cell’s fill with…
- Excel VBA Optional Arguments: How They WorkYes—Excel VBA procedures and functions can have optional arguments. Declare one with the Optional keyword; give typed arguments…
- Excel Macro Programming: What It Is and How It WorksExcel macro programming means creating instructions that automate tasks in a workbook, most commonly with Visual Basic for…
- Easy Macro Recorder in Excel: How to Record and Use OneYes. Microsoft Excel for Microsoft 365 includes a built-in macro recorder: choose Developer > Record Macro, perform the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.