Posts by Mark_11

Re: Looping through sheets and workbooks using array

Stephen, I solved it - eventually. It required a redim statement as well as defining the array for WB_temps. Thanks for your input.

Re: Looping through sheets and workbooks using array

No Stephen, I have changed the sheet names but the logic is the same and they've worked before with an array.
It appears to be the Workbook part that is causing the problem. Actually I've been trying a number of solutions and mistakenly put the wrong part in error - apologies for that (the irritation is affecting my focus). It is the line:

Set WB_temps(Cnt) = Workbooks.Open("schools_data_" & (Cnt)& ".xlsx")

that is causing an error, which is noted as <Subscript out of range> after first opening successfully the first workbook.

Apologies again,
Mark

Re: Looping through sheets and workbooks using array

Yes Stephen they are just typos caused by hacking out loads of irrelevant code.
It is the Set School_index = WB_temps etc that doesn't work. The Sheets part is ok, but it doesn't like the WB_temps(Cnt) part.
Thanks
Mark

This is grinding me down and appears to be a simple issue. I'm trying to loop through 4 workbooks that are opened alongside other books, then from the same named sheets from each of the 4, copy rows. It's not working. Any thoughts would be appreciated.

Sub School_Generator_2015()
 
Dim WB_temps() As Workbook
Dim Cnt as Integer, Sheet_cnt as integer
Dim Sheet_names() As Variant, School_Index As Range
     
Sheet_names() = Array("sheet1", "sheet2", "sheet3")




For Cnt = 0 To 3
        Set WB_temps(Cnt) = Workbooks.Open("schools_data_" & (Cnt)& ".xlsx") 'simplified path but this works
Next Cnt


 
For Cnt = 0 To 3
       For Sheet_cnt = 0 To 2
       Set School_Index = WB_temps(Cnt).Worksheets(Sheet_names(sheet_cnt)).Range("A1:A900") ‘<--does not work


         ‘now copy rows etc 
      
       Next Sheet_cnt
Next Cnt