Open and Set a workbook

[cknnugget]

Trying to open a workbook, set it, then set a worksheet and list object. When I step into Set WB2, it opens the workbook but it does not set WB2 as the workbook. The next step into "MDB", I get subscript out of range. What am I doing wrong? How to I open and declare a workbook correctly?

Dim WB1 As Workbook
Dim WB2 As Workbook
Dim MI As Worksheet
Dim FP As Worksheet
Dim MDB As Worksheet
Dim ML As ListObject
Dim BT As ListObject
Dim WB2A As String

Set WB1 = ActiveWorkbook
Set MI = WB1.Sheets(Sheet2.Name)
Set FP = WB1.Sheets(Sheet10.Name)
Set BT = MI.ListObjects("Pending")
Let WB2A = FP.Range("C2")
Set WB2 = Workbooks.Open(WB2A)
Set MDB = WB2.Worksheets(Sheet5.Name)
Set ML = MDB.ListObjects("MEMOLog")

 

[6StringJazzer]

it does not set WB2 as the workbook.

How do you know this? If this were true then you would not even get a subscript error, you would get a different error before the subscript could even be evaluated.

The code up to that point looks like it should work but it's convoluted. What is ActiveWorkbook at the time this code runs? If it is the same as the workbook containing the code, I would rewrite this.

Sheet5 is the codename of a worksheet in the workbook containing this code. Is that what you intend?

The thing that will cause that error is if there is no worksheet in the file just opened as WB2 that is named Sheet5.Name. Have you run this in debugging mode and checked the values of these things at the point where they are referenced?

 

[cknnugget]

There is a sheet5 in the workbook that is opened (WB2). When I changed sheet5 to the actual name, the macro ran without error.

The code up to that point looks like it should work but it's convoluted. What is ActiveWorkbook at the time this code runs? If it is the same as the workbook containing the code, I would rewrite this.

This is my first macro project, multiple macros within the project. In this macro, I am opening a second workbook to copy/ paste from the main WB1 to WB2. Still learning the correct way to open and use additional Workbooks.

 

[6StringJazzer]

Unfortunately your response did not directly address my questions so I can't offer any further progress.

Please note for terminology that codename is the name of the sheet as shown in the VBA project navigator. The tab name is the name the user sees on the worksheet tab. They are not necessarily the same. You can use the codename as a Worksheet object for qualification such as what you have done for
Sheet5.Name
This expression returns the tab name for Sheet5.

When I changed sheet5 to the actual name, the macro ran without error.

I do not understand what you mean by this. Sheet5 is used as a codename in your code. You can't just change it without completely changing the syntax you are using and I don't know what you mean by "actual name." It would be more helpful if you showed the actual code that ran without error.

If the code ran without error, are you now getting the results you want? Or do you still have a problem?

 

[cknnugget]

Apologies for the confusion. What you had said put me on the right path. Thank you for your assistance.