none
"Index refers beyond end of list" when workbook contains more than 3 worksheets. RRS feed

  • Question

  • The following code causes a download of data from a specific worksheet of a workbook stored on a SharePoint.  But, when the workbook contains more than 3 worksheets, the code crashes and states "Index refers beyond end of list".  Just the addition of a blank (or data filled) 4th sheet will cause the code to crash as mentioned above.  Removing the 4th sheet will cause the code to work without error.

    Here is the code:

    -----------------------------------------------------------------------------------

    Public Sub ImportBFSCAN()

        On Error GoTo Err_ImportBFSCAN
        
        ' Define Workbook and Worksheet Variables
        Dim wkbMyWorkbook As Workbook
        Dim wkbWebWorkbook As Workbook
        Dim wksWebWorkSheet As Worksheet
        
        Set wkbMyWorkbook = ActiveWorkbook
        
        ' Open The Web Workbook
        
        'Go to SharePoint and open workbook
        Workbooks.Open ("websitenameandfilename.xlsm")
        
        ' Set the Web Workbook and Worksheet Variables
        Set wkbWebWorkbook = ActiveWorkbook
        Set wksWebWorkSheet = wkbWebWorkbook.Sheets("Issues Entry Form")
        
        ' Copy The Web Worksheet, delete if already exists in this Workbook the Insert and ename
        wksWebWorkSheet.Copy after:=wkbMyWorkbook.Sheets(Sheets.Count)
        wkbMyWorkbook.Activate
        Call DeleteBFSCAN
        wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "BFSCAN"
        
        ' Close the Web Workbook
        wkbMyWorkbook.Activate
        wkbWebWorkbook.Close False

    Exit_ImportBFSCAN:
        Exit Sub
        
    Err_ImportBFSCAN:
        MsgBox Err.Description
        Resume Exit_ImportBFSCAN

    -----------------------------------------------------------------------------------

    I wish to use the above code with a workbook having 4-5 worksheets, but the code only works for workbooks having a maximum of 3 worksheets.

    I would very much appreciate any support this group could provide.

    Thank you,

    Rich

    Friday, May 1, 2015 4:26 PM

Answers

  • Re:  error after adding worksheets

    I believe your problem lies in the DeleteBFSCAN code.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, May 1, 2015 7:28 PM
  • Thanks Jim!

    It seems that the order of the commands leading up to "Call DeleteBFSCAN" was causing the issue.  I have re-order the commands as follows:

        wkbMyWorkbook.Activate
        Call DeleteBFSCAN

        wksWebWorkSheet.Copy after:=wkbMyWorkbook.Sheets(Sheets.Count)
        wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "BFSCAN"

    I find it strange that the previous order provided for successful downloads if the worksheet count was no more than 3, but failed when the worksheet was greater than 3.  This new order of commands works, regardless of the number of worksheets in the source workbook.

    Thank you very much for your help!

    Rich


    Saturday, May 2, 2015 11:04 AM

All replies

  • Re:  error after adding worksheets

    I believe your problem lies in the DeleteBFSCAN code.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, May 1, 2015 7:28 PM
  • Thanks Jim!

    It seems that the order of the commands leading up to "Call DeleteBFSCAN" was causing the issue.  I have re-order the commands as follows:

        wkbMyWorkbook.Activate
        Call DeleteBFSCAN

        wksWebWorkSheet.Copy after:=wkbMyWorkbook.Sheets(Sheets.Count)
        wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "BFSCAN"

    I find it strange that the previous order provided for successful downloads if the worksheet count was no more than 3, but failed when the worksheet was greater than 3.  This new order of commands works, regardless of the number of worksheets in the source workbook.

    Thank you very much for your help!

    Rich


    Saturday, May 2, 2015 11:04 AM