Choosing a specific Workbook and worksheet from cell values

[ChemistB]

With the workbook name in B1 and worksheet name in B2 of active sheet. (I will have a second workbook and sheet in B3 and B4). I want to be able to choose those other workbooks so I can manipulate them. The workbooks will be open so don't need to worry about path.

Why doesn't this code work

Sub test()
Dim Wkbk1 As Workbook
Dim WkSh1 As Sheets
Set Wkbk1 = Workbooks(ActiveSheet.Range("B1").Value)
Set WkSh1 = Sheets(ActiveSheet.Range("B2").Value)
MsgBox (Wkbk1.WkSh1.Range("A1").Value)

End Sub

[YasserKhalil]

May be

Sub Test()
    Dim Wkbk1 As Workbook
    Dim WkSh1 As Worksheet

    Set Wkbk1 = Workbooks(ActiveSheet.Range("B1").Value)
    Set WkSh1 = Wkbk1.Worksheets(ActiveSheet.Range("B2").Value)

    MsgBox (WkSh1.Range("A1").Value)
End Sub

In the declare of WkSh1, you would declare as Worksheet not as collections
And for assigning the worksheet, you would refer to the workbook
Hope this helps you

[ChemistB]

Ahhhh, thanks. that works perfectly.

[YasserKhalil]

You're welcome. Glad I can offer some help
Thanks for the feedback and for the rep points