sheet array subscript out of range

Question

I am creating a macro which will select sheets from a larger workbook, move and save those sheets as a new workbook, and then move onto the next set.

I have created a pseudo "array" with start and end values (designated by sheet index numbers).

I am encountering a "Subscript out of range" error after completing the section that saves the file, but before the loop which would pull the next set of worksheets.

Below is my code. Any help with this error would be appreciated.

Dim Start As Integer
Dim Finish As Integer
Dim SR As Integer  
Dim SC As Integer
Dim ER As Integer
Dim EC As Integer
SR = 2
SC = 5
ER = 2
EC = 6
Start = Sheets("REF").Cells(SR, SC).Value
Finish = Sheets("REF").Cells(ER, EC).Value
Dim sheetArray() As Double
Dim i As Integer
Dim c As Integer
i = 0
c = Start
lastrow = Cells(100, SC).End(xlUp).Row

Do Until SR = lastrow

    Do Until c > Finish
        ReDim Preserve sheetarray (0 to i)
        i = i + 1
        c = c + 1
    Loop

    Sheets(sheetarray).Copy
    ActiveWorkbook.SaveAs Filename:= _ XXXXXXXXXXXXXXXXXX

    C = Start
    i = 0
    SR = SR + 1
    ER = ER + 1
Loop

EDIT: 16:35 Central US

Currently, the relevant code block matches what is above, through the line lastrow = Cells(100, SC).End(xlUp).Row

Do Until SR = lastrow

ReDim sheetArray(i)

Do Until c > Finish
    ReDim Preserve sheetArray(i)
    sheetArray(i) = c
    i = i + 1
    c = c + 1

Loop



Sheets(sheetArray).Copy
ActiveWorkbook.SaveAs Filename:= _
    XXXXXXXXXXXXX

c = Start
i = 0
SR = SR + 1
ER = ER + 1

Loop

Answer 1

As I could see it, the problem is that you are just adjusting the dimension of your sheetArray, but you are not putting anything inside. So basically, the values inside the array are all zeros. Then you are asking Excel to copy sheets(0), which is out of range because sheet numbers start at 1.

You can fix this by writing inside your array the indices of the sheets you want to copy:

Do Until c > Finish
    ReDim Preserve sheetarray (0 to i)
    sheetarray(i) = c ' <~~~~ or something else, according to your goal
    i = i + 1
    c = c + 1
Loop

p.s.: It is better to make sheetArray an array of Integer (not Double), since its elements are indices of sheets...However, even with doubles it should work if the array's contents are set properly.

Answer 2

You will need three things here:

1. ReDim the array before you load each sheet index because the way you have it now it will just keep building on each loop and thus you will get the Subscript out of range error starting at the second loop - because the array basically has, as an example, 1 3 5 from first and then 1 3 5 3 7, with 1 3 5 from first then 3 7 from second.
2. To set the value of the array each time. You only set the elements of the array
3. Qualify which workbook to copy the sheets from, because each time you copy the sheets it sets the active workbook to the newly copied workbook.

Build your Do Loop block like this:

Do Until SR = lastrow

    ReDim sheetArray(0) 'or you can put i here since you set it to zero at the bottom

    Do Until c > Finish

        ReDim Preserve sheetArray(i)
        sheetArray(i) = c

        i = i + 1
        c = c + 1

    Loop

    Workbooks("myWkb").Sheets(sheetArray).Copy 'where myWkb is the workbook name you need ... you can also use ThisWorkbook (meaning the workbook where the code is running) but this is not best practice
    ActiveWorkbook.SaveAs Filename:="XXXXXXXXXXXXXXXXXX"

    c = Start
    i = 0
    SR = SR + 1
    ER = ER + 1

Loop