Select an Excel Worksheet by Variable Index in VBA
Question details
Access an Excel worksheet using an index stored in another workbook, without relying on Select or Activate.

- 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.
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.
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.
In Excel, open Developer > Visual Basic, then open the module containing your macro.
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
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.

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.

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).




