+ Reply to Thread
Results 1 to 10 of 10

Run-time Error '9': Subscript out of Range

  1. #1
    Registered User
    Join Date
    01-10-2014
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    10

    Run-time Error '9': Subscript out of Range

    Hi All,

    I am new to Macros in Excel.

    I am running the below code and it's throwing the run-time error- 9. Pls help with
    the below issue.

    Please Login or Register  to view this content.

    I am not using any kind of array. I am unable to understand the problem.
    Last edited by Veena Anoohya; 01-22-2014 at 04:58 AM.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Run-time Error '9': Subscript out of Range

    You don't appear to open the workbooks as part of your loop...

    Try adding workbooks.open(filename) after the do while line

  3. #3
    Registered User
    Join Date
    01-10-2014
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Run-time Error '9': Subscript out of Range

    Thanks for replying

    I have added the below statement after your reply, but still the code is throwing the same error.

    'Workbooks.Open (directory & fileName)

    When clicked Debug button, the below line is highlighted-

    'total = Workbooks("import-sheets.xls").Worksheets.Count

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Run-time Error '9': Subscript out of Range

    Hi - is there a workbook called "import_sheets.xls" open when you run the macro? I think it would be case sensitive.

  5. #5
    Registered User
    Join Date
    01-10-2014
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Run-time Error '9': Subscript out of Range

    Quote Originally Posted by yudlugar View Post
    Hi - is there a workbook called "import_sheets.xls" open when you run the macro? I think it would be case sensitive.
    Hi,
    I have not created any such sheet .

  6. #6
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Run-time Error '9': Subscript out of Range

    Try altering the two instances of Workbooks("import-sheets.xls") to Workbooks(filename"), I havent used these code formats myself before but generally the error message says that the macro cannot find what it is looking for.
    Hope this may help
    Jmac1947

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Run-time Error '9': Subscript out of Range

    Try this.
    Please Login or Register  to view this content.
    Last edited by Norie; 01-12-2014 at 05:04 AM.
    If posting code please use code tags, see here.

  8. #8
    Registered User
    Join Date
    01-10-2014
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Run-time Error '9': Subscript out of Range

    Quote Originally Posted by Norie View Post
    Try this.
    Please Login or Register  to view this content.
    Hi Norie,

    Thanks for the reply, I have used your code and it's working


    The only problem is that I am not getting any output result in ThisWorkbbok(Current workbook).

    The output should be like the sheets of other workbooks of the same path, should get added
    at the last of the current Workbook(ThisWorkbook) sheets.


    But after the code runs successfully, there is no such output found.



    Actually, I have slighlty modified the code.Please help for this code.




    Please Login or Register  to view this content.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Run-time Error '9': Subscript out of Range

    The code I posted works fine for me, provided there are Excel workbooks in the specified directory.

    It will copy all the worksheets from all the workbooks in the directory into the workbook which houses the code.

    By the way, the modification you've made to the code would mean that all the worksheets from each source workbook would be copied to the destination workbook x times, where x is the no of worksheets in the source workbook.

    For example if the source workbook had 3 worksheets those 3 worksheets would be copied 3 times.

  10. #10
    Registered User
    Join Date
    01-10-2014
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    10

    Thumbs up Re: Run-time Error '9': Subscript out of Range

    Quote Originally Posted by Norie View Post
    The code I posted works fine for me, provided there are Excel workbooks in the specified directory.

    It will copy all the worksheets from all the workbooks in the directory into the workbook which houses the code.

    By the way, the modification you've made to the code would mean that all the worksheets from each source workbook would be copied to the destination workbook x times, where x is the no of worksheets in the source workbook.

    For example if the source workbook had 3 worksheets those 3 worksheets would be copied 3 times.
    Thanks Norie.
    The code is working fine

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] subscript out of Range (Run time error 9), need help
    By shiva_reshs in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-05-2013, 02:31 PM
  2. Run-time error 9: Subscript out of Range
    By boesingen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-14-2013, 09:41 AM
  3. run time error 9 : subscript out of range
    By baxtercavendish in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 08-28-2009, 11:14 AM
  4. run time error 9, subscript out of range
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-06-2006, 09:31 PM
  5. Run time error-subscript out of range
    By ldd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-21-2006, 01:37 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1