VBA Open another workbook and variable sheet

Question

I still could not find the answer:

I have a sheet with the name of the Client in cell A2 and the product code in cell B2. I have a file "C: \ Users \ Reception \ Documents \ Shared \ Item Master Data \ Customer BOMS \" with a book for each Client. These books have a tab on the product code with the recipe for creating the product.

What I need to do is tell Macrom to open the book for a specific client in cell A2 and then go to the tab in cell B2.

I managed to open the book, but not the sheet. Here is my code:

Range("A2").Select

Dim CName As String

Dim PCode As String

Dim BOM As Workbook

Dim ws As Worksheet

CName = ActiveCell.Value

PCode = ActiveCell.Offset(0, 1).Value

Set BOM = Workbooks.Open("C:\Users\Reception\Documents\Shared\Item Master Data\Customer BOMS\" & CName & ".xlsm")

Set ws = BOM.Sheets(Range(PCode))

Answer 1

Try the following:

Range("A2").Select

Dim CName As String

Dim PCode As String

Dim BOM As Workbook

Dim ws As Worksheet

CName = ActiveCell.Value

PCode = ActiveCell.Offset(0, 1).Value

Set BOM = Workbooks.Open("C:\Users\Reception\Documents\Shared\Item Master Data\Customer BOMS\" & CName & ".xlsm")

Set ws = BOM.Sheets(PCode)
 ws.activate