Activate Worksheet doesn't work with two open workbooks

[bpatters]

I have a program with two open workbooks -- one has data in it, the other has parameters for running the program. All code is in personal.xlsb.

I have to switch between the two workbooks during the processing loop, but I can't activate the worksheet that's on the bottom. Here's the code that represents it, below is the results.

Please Login or Register  to view this content.

Here are the results:

If the Parms sheet is open on top:

set MyBook active Parms.xlsm RunParms
Set PBook active Parms.xlsm RunParms

If the Registered sheet is open on top:

set MyBook active 50K Records.xlsm Registered
Set PBook active 50K Records.xlsm Registered

If I explicitly activate the sheets, as in the code below, I get a runtime error 9 subscript out of range on whichever sheet is underneath:

Please Login or Register  to view this content.

Thanks for your help.

[skywriter]

I'm not sure what you're trying to do but let's start with the variable declarations.

Please Login or Register  to view this content.

Only PSheet is declared as a worksheet which means only PSheet is explicitly a worksheet, MySheet is going to be a variant.
There is a common mistaken belief that typing more than one variable and as "type" at the end makes them all that type, that is not the case.

When you declare a workbook explicitly using a file name that is correct and that is how you should leave it, then you can activate that workbook using the variable name.
Using set = activeworkbook means you are now pointing that variable to whatever the active workbook is, it's not activating the workbook.

[jolivanes]

Please Login or Register  to view this content.

Maybe explain what you are trying to achieve.

[leelnich]

@bpatters - ActiveSheet & ActiveWorkbook are read-only properties of a "containing" object.
To change the "Active" object, use the Activate method:

Please Login or Register  to view this content.