use worksheet name stored in a cell to call a cell from that worksheet-CodePudding

I need to pull data from a cell on another worksheet but I don't know what the name of the worksheet will be. However, the name of the worksheet will be stored in one of the cells on the main sheet when its generated.

A11 = myNewWorksheet

I need cell G3 from myNewWorksheet that was just created by a macro. The worksheet name is stored in cell A11 on the main worksheet.

I'm not sure how to call the new worksheet name from cell A11 to populate the data from cell G3 in the newly created worksheet.

='$A11'!G3

Can anyone help me with this?

Thanks in advance

CodePudding user response：

You will want to use the INDIRECT function

=INDIRECT(A11&"!G3")

INDIRECT is passed a string which it converts to a range reference. In this case we pass it a string consisting of the value in cell A11 concanetated (&) with the remainder of the address which is "!G3"

CodePudding user response：

Set the workbook with your in-cell value as a string. You could do it in 1 or 2 lines, but this is easier to follow.

Sub IdentifyWorksheet()
    Dim WorksheetName As String
    Dim TargetWorksheet As Worksheet
    WorksheetName = ActiveSheet.Range("A11").Value
    Set TargetWorksheet = Worksheets(WorksheetName)
End Sub

CodePudding user response：

Try below code

Debug.Print Worksheets(Worksheets("Main").Range("A11").Value).Range("G3").Value