locked
VBA Script Help Excel: Get Subscript Out of Range RRS feed

  • Question

  • 2 days ago the code below worked-

    Workbooks.Open ("InactiveServers")
    Worksheets("Inactive").Copy After:=Workbooks("WorkingCopy").Worksheets(3)

    Now I get the "Run Time Error '9': Subscript out of range" notice.

    No changes have been made to the code.  I have tried number references, sheet names, etc.

    When I tried to use Ubound to check the range of the worksheets array it kept telling me that I needed to supply an array name.

    This problem is weird and any help would be appreciated...

    • Moved by Reed KimbleMVP Thursday, August 18, 2011 8:56 PM vba quenstion in vb general (From:Visual Basic General)
    Thursday, August 18, 2011 4:49 PM

All replies

  • How many sheets are, now, in WorkinCopy? Does it exists? Can you stop the execution and verify if 'inactive' sheet exists?
    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    Thursday, August 18, 2011 5:23 PM
  • In WorkingCopy there are 3 sheets.  It exists and the script has been doing work on it before this point.  Also the "Inactive" sheet exists but is an empty sheet - the script opens the  "InactiveServers" workbook.
    Thursday, August 18, 2011 5:41 PM
  • this is really weird. Try to isolate the problem assigning any object to a variable

    set inactWks = workbooks("InactiveServers")

    set inactWsh = inactWks("Inactive")

    set wcWks = Workbooks("WorkingCopy")

    set wcWhs = wcWks(3)

    inactWsh.Copy After:=Workbooks("WorkingCopy").Worksheets(3)


    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    Thursday, August 18, 2011 6:44 PM
  • So it looks like one of the recent security patches has broken Workbooks.  After trying what you suggested (it did not work as it could not find Workbooks("InactiveServers") I went and looked at my restore points.  I had three windows updates intalled, one on the 16th, one on the 17th and one this morning.  Restiored to the 15th and the script worked fine.

    Thanks for your help.

    We will need to track this down and let the patch makers know.

    Aaron.

    Thursday, August 18, 2011 8:15 PM
  • This is the wrong forming for VBA - moving to appropriate forum...
    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"
    Thursday, August 18, 2011 8:55 PM