title: How to define an object inside a for loop for excel vba
body: <p>I want to import data from multiple workbooks, all from the same sheet index (3). 
  I'm new to vba, and I figured out how to open multiple files up, and also to copy data from one sheet to another sheet in a different workbook for a single file, but I can't seem to figure out how to do that for multiple files.
  I highlighted where the error is, it tells me "object doesn't support this property or method"</p>
  
  <p>Could you please help?
  Thanks</p>
  
  <pre><code>Sub dataimport()
  
  ' Set Vars
  Dim ArbinBook As Workbook, DataBook As Workbook
  Dim i As Integer, j As Integer
  Dim Caption As String
  Dim ArbinFile As Variant, DataFile As Variant
  
  
  ' make weak assumption that active workbook is the target
  Set DataBook = Application.ActiveWorkbook
  
  ' get Arbin workbook
  Caption = "Please select an input file"
      ' To set open destination:
      ' ChDrive ("E")
      ' ChDir ("E:\Chapters\chap14")
      ' With Application
  
  'Set "arbinfile" as variant, the "true" at end makes it into an array
  ArbinFile = Application.GetOpenFilename(, , Caption, , True)
  
  'Exit when canceled
  If Not IsArray(ArbinFile) Then
      MsgBox "No file was selected."
      Exit Sub
  End If
  
  Dim targetSheet As Worksheet
  Set targetSheet = DataBook.Sheets(1)
  
  
  'Open for every integer i selected in the array "arbinfile"
  For i = LBound(ArbinFile) To UBound(ArbinFile)
          Set ArbinBook = Workbooks.Open(ArbinFile(i))
  
  
  targetSheet.Range("A2", "G150").Value = ArbinBook.Sheets(3).Range("A2", "G150").Value
  
     **ERROR at the line above**   
  
          Workbooks(DataSheet).Activate                        'Reactivate the data book
          Worksheets(1).Activate                               'Reactivate the data sheet
          ActiveWorkbook.Sheets(1).Copy _
             after:=ActiveWorkbook.Sheets(1)
          Workbooks(ArbinFile(1)).Activate                 'Reactivate the arbin book(i)
  
  
          ArbinBook.Close
  
  Next i
  Beep
  
  End Sub
  </code></pre>