VBA & Macro Problems

Select an Excel Worksheet by Variable Index in VBA

Olivia MillerOlivia Miller Nov 20, 2026 869 views

Question details

Access an Excel worksheet using an index stored in another workbook, without relying on Select or Activate.

Select an Excel Worksheet by Variable Index in VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A VBA macro reads a worksheet number from one workbook and uses it to access a sheet in another workbook.
Observed behavior
The macro needs to identify the target worksheet by a variable index; selecting a range after activating the sheet can produce an error.
Before you start

Open both workbooks in Excel and confirm which worksheet contains the index in cell G10. Check that the number corresponds to a worksheet position in the target workbook.

Solution 1Recommended

Reference the target worksheet directly by its numeric index

Use this approach to read or write cells on the target worksheet without changing the active workbook or selection.

Qualify both the source cell and the target worksheet with their workbook references. Worksheets(N) counts worksheets by position and excludes chart sheets.

1
Open the VBA editor

In Excel, open Developer > Visual Basic, then open the module containing your macro.

2
Set the workbook and sheet references

Use the following example, replacing Sheet1 with the source worksheet name and Periods.xlsm with the open target workbook name: Sub TransferCurrentPeriod() Dim sourceWb As Workbook Dim targetWb As Workbook Dim targetWs As Worksheet Dim sheetNumber As Variant Dim N As Long Set sourceWb = ThisWorkbook Set targetWb = Workbooks("Periods.xlsm") sheetNumber = sourceWb.Worksheets("Sheet1").Range("G10").Value If IsError(sheetNumber) Then MsgBox "G10 contains an error." Exit Sub End If If Not IsNumeric(sheetNumber) Then MsgBox "Enter a worksheet number in G10." Exit Sub End If If CDbl(sheetNumber) < 1 Or CDbl(sheetNumber) > targetWb.Worksheets.Count Then MsgBox "The worksheet number is out of range." Exit Sub End If If CDbl(sheetNumber) <> Fix(CDbl(sheetNumber)) Then MsgBox "Enter a whole worksheet number." Exit Sub End If N = CLng(sheetNumber) Set targetWs = targetWb.Worksheets(N) Debug.Print targetWs.Range("A10").Value End Sub

3
Run the macro

Run the macro from the VBA editor and check the Immediate window using View > Immediate Window. Replace Debug.Print with the cell operation your macro needs.

Reference the target worksheet directly by its numeric index
When you need to select a cell: Selection is usually unnecessary. If you need to show A10 to the user, activate the target workbook and visible worksheet before calling targetWs.Range("A10").Select. Hidden sheets cannot be activated until they are made visible.
Free Microsoft Office alternative

Work with Excel files in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and editing Excel files. Its format support helps you move existing workbooks into your workflow; check macro availability and compatibility before relying on VBA.

Open and edit Microsoft Excel XLSX and XLSM filesNavigate and edit worksheets in a familiar spreadsheet interfaceWork with existing Office documents during migrationUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

How do I access a worksheet using a variable in VBA?

Store a valid worksheet position in a Long variable, then use a qualified reference such as Workbooks("Periods.xlsm").Worksheets(N). The target workbook must be open.

Why can Range("A10").Select fail after activating a sheet?

Select depends on the active workbook and worksheet, and hidden sheets cannot be activated. Use targetWs.Range("A10") directly when you only need to read or write the cell.

Does Worksheets(N) include chart sheets?

No. Worksheets(N) indexes only worksheets. Sheets(N) includes all sheet types, so the same number can refer to a different item.

What happens if the worksheet index is invalid?

An index below 1 or greater than the target workbook's Worksheets.Count causes an error. Validate the number before using Worksheets(N).