vba: run time error '9'

Question

When I want to copy the values of cells from one sheet to anoter in 6 Workbooks which are opened (in my case from "Sheet1" cells (B9:E111) to "Sheet2") I'm standing in front of the error: 'run time error 9 : Subscript out of range' this is the code I wrote:

Sub sbCopyRangeToAnotherSheet()

  For i = 1 To 6

     Workbooks(i).Worksheets("Sheet2").Range("A1").Value = Workbooks(i).Worksheets("Sheet1").Range("B9:E111").Value  

  Next i

End Sub

May someone can help me? Thank you

Answer 1

If all of your workbooks are open (the code below can work also if only 1, 2, or 3 are open), it will copy the values from Range("B9:E111") in "Sheet2" and paste them to "Sheet1" from Cell "A1".

Sub sbCopyRangeToAnotherSheet()

Dim i As Integer
Dim wb() As Workbook

' work with dynamic number of current open workbooks
ReDim wb(1 To Application.Workbooks.count)

For i = 1 To Application.Workbooks.count
    Set wb(i) = Workbooks(i)
    wb(i).Worksheets("Sheet1").Range("B9:E111").Copy
    wb(i).Worksheets("Sheet2").Range("A1").PasteSpecial xlValues
Next i

End Sub

Edit1:

Sub sbCopyRangeToAnotherSheet()

Dim i As Integer
Dim wb() As Workbook

' work with dynamic number of current open workbooks
ReDim wb(1 To Application.Workbooks.Count)

For i = 1 To Application.Workbooks.Count
    Set wb(i) = Workbooks(i)
    wb(i).Worksheets(1).Range("B9:E111").Copy
    wb(i).Worksheets(2).Range("A1").PasteSpecial xlValues
Next i

End Sub

Answer 2

"Subscript out of range" means you're accessing an array/collection beyond its boundaries.

Workbooks(i).Worksheets("Sheet2").Range("A1").Value = Workbooks(i).Worksheets("Sheet1").Range("B9:E111").Value  

I count several different places in that single instruction that could throw that error. Split it up.

Dim book As Workbook
' if it blows up here, check how many books you have open:
Set book = Workbooks(i) 'consider looping from 1 To Workbooks.Count instead of 1 To 6

Dim source As Worksheet
' if it blows up here, check the filename of the book and whether it has a "Sheet1":
Set source = book.Worksheets("Sheet1") 

Dim destination As Worksheet
' if it blows up here, check the filename of the book and whether it has a "Sheet2":
Set destination = book.Worksheets("Sheet2")

' when it blows up here, consider exactly what you're trying to do:
destination.Range("A1").Value = source.Range("B9:E111").Value

The last instruction looks suspicious to me. If you're trying to paste Sheet1!B9:E111 into Sheet2!A1, consider using Copy+PasteSpecial as in Shai Rado's answer.

If you mean to iterate all open workbooks, consider a For Each loop instead:

Dim book As Workbook
For Each book In Workbooks
    '...
Next

Answer 3

Try this, I think it is a problem to put the value of the whole range into one cell. I assign it first to an Array and then put the array on from what is A1 + the upper boundaries of the array...

Sub sbCopyRangeToAnotherSheet()

Dim vArr() As Variant

  For i = 1 To 6

     vArr = Workbooks(i).Worksheets("Sheet1").Range("B9:E111").Value
     With Workbooks(i).Worksheets("Sheet2")
        .Range(.Cells(1, 1), .Cells(UBound(vArr, 1), UBound(vArr, 2))).Value = vArr
     End With
  Next i

End Sub

Answer 4

I'll warn that using workbook indexes like this is risky, but try this..

Sub sbCopyRangeToAnotherSheet()

   For i = 1 To 6
       Workbooks(i).Worksheets("Sheet1").Range("B9:E111").Copy Workbooks(i).Worksheets("Sheet2").Range("A1")
   Next i

End Sub

Answer 5

Are you trying to do something like this:

Sub sbCopyRangeToAnotherSheet()

  For i = 1 To 6

     Workbooks(i).Worksheets("Sheet2").Range("A1").Value = worksheetfunction.sum(Workbooks(i).Worksheets("Sheet1").Range("B9:E111"))

  Next i

End Sub