Inexplicable Subscript out of range error forWorksheet reference

[svendfj]

Thanks in advance for any help. I have never seen anything like this before. I have a Workbook ("WBName") with VBA code that has been working fine for months. Now, for no apparent reason, it gets stuck on the following statement:

Set sh1 = Workbooks("WBName").Worksheets("MAIN")

The debugger says:
Workbooks("WBName").Works...= <subscript out="" of="" range=""><subscript out="" of="" range="">Subscript out of range

Which is crazy, because the Worksheet "MAIN" is still there like it always has been. I run across the same error from identical, earlier versions of the program that had worked fine. Also, the problem is the same for any legitimate worksheet name referenced within the parentheses. (That is "MAIN" can be replaced with the name of any other worksheet in the WB, and I get the same error.

Thanks again, Sven

</subscript></subscript>

 

[JLGWhiz]

Maybe it is the workbook that is the problem, instead of the worksheet, Is the Workbook open? If WBName is a variable and you have it enclosed in quote marks, it will throw the error.

 

[svendfj]

Maybe it is the workbook that is the problem, instead of the worksheet, Is the Workbook open? If WBName is a variable and you have it enclosed in quote marks, it will throw the error.

Thanks JLGWhiz. "WBName" is just the current name of the main workbook (that I have simplified to "WBName").

Also, for example, the following statement also suddenly now does not work:
Workbooks("WBName").Activate

This statement is in a loop that copies from a series of excel documents in a special folder and pastes the information into the respective worksheets of the WB. After copying from each excel document, this statement has been the means to get back to the main WB.

I have a repository of prior WBs that I have saved, going back weeks, each the same except that the data for each week varies. These all worked perfectly at the time, and never generated any error such as what I am getting now. But now, mysteriously, they all show the same problem now.

Thanks again, Sven

 

[JLGWhiz]

Unless you have your system set to hide file extensions, the workbook name will need the file extension added (.xls, xlsx, xlsm, etc.) That is why I thought "WBName" might be a variable.