How to refer a cell of another sheet mentioned in a cell through VBA

Question

I am having a excel file named "Book1" with "Sheet1" and "Sheet2". In Sheet1, Cell Value of A1 is "'[Book1.xlsb]Sheet2'!$A$14", which is derived as some formula result. I want cell reference mentioned in cell A1 to be selected through VBA.

I have put VB Code as

Range(Range("A1")).Select or Range([Indirect("A1")]).Select

This code works only when cell referred in A1 is in same sheet, but it doesn't work if cell referred is in different sheet

Can someone help to solve this please

Solution 1

you can do either way of following ones:

With Worksheets("Sheet2")
    .Activate
    .Range([Indirect("Sheet1!A1")]).Select
End With

or

With Worksheets("Sheet2")
    .Activate
    .Range(Worksheets("Sheet1").Range("A1")).Select
End With

Solution 2

Since both worksheets ("Sheet1" and "Sheet2") are in the same workbook, the value in "Sheet1" Cell A1 should be "Sheet2!$A$14".

The code below is a little longer then it needs to be, but I wanted to go through all the steps to explain better.

I am using the Split function to seperate the Worksheet name and the Range.Address, and put the result in 2 array elements.

Afterwards, you retrieve the first array RngString element as the worksheet's name, and then second array element is the range.address.

Code

Option Explicit

Sub SelectRange()

Dim Rng As Range
Dim RngString As Variant
Dim ShtName As String
Dim RngAddress As String

RngString = Split(Worksheets("Sheet1").Range("A1").Value, "!")

' sheet name is the first array element
ShtName = RngString(0)

' Range address is the second array element
RngAddress = RngString(1)

' setting the Rng object
Set Rng = Worksheets(ShtName).Range(RngAddress)

' since your range is in another worksheet, you need to activate it first
Worksheets(ShtName).Activate
' select the range
Rng.Select

End Sub