You are here:

Excel/Inexplicable 'Subscript out of range' error for Worksheet reference



Thanks in advance for any help you can lend.  I have a workbook that when you click on button "Load", VBA code cycles through the text files in a certain folder and copies the data (with the data on one text file being copied into one worksheet in the main workbook).  From there, the information is aggregated on a Master spreadsheet.  The program has been working fine for many months.

When developing the VBA code, I learned that the best approach was to open each file within Excel.   I had tried other approaches, and this was the one that worked perfectly.  This was many months ago, and I have been generating a new version of this workbook each week.  Only the data imported from the text files changes week to week.  The Workbook and the VBA code have otherwise been unchanged and have continued to work absolutely fine - until today.  

Now, the following statement suddenly does not work:
   Workbooks("WBName").Activate      where I have substituted "WBName" for the current name of main workbook

This statement is in a loop that I alluded to, that copies from a series of text documents in a special folder and pastes the information into the respective worksheets of the WB. The code opens each text file within Excel, as an Excel document.  The text file is then closed, and the statement above has been the means to get back to the main WB.

I have a repository of prior WBs, 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 described above.

I very much appreciate any suggestions you may have.  

Thanks, Sven


This sounds like a problem with a windows setting. It has been a long time since I played with this but:

The success enjoyed when not using the extension is dependent on a windows level setting
- folders setting to hide known extensions.

So I believe that setting has changed and now excel does not recognize your workbook name.  


(use the proper extension for your workbook)  should always work regardless of the setting.  

Workbooks("Myworkbook")  only works when the setting is set to hide known extensions.  

This all assumes that your example, which did not show an extension, is actually how you are addressing the workbook.

So if you don't add the extension in your code, then you would need to change the setting I highlighted to hide known extension.

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.