title: Pulling data from a closed workbook macro
body: <p>I've been having issues concerning pulling data from a closed workbook by using a macro.</p>
  
  <p><img src="https://i.stack.imgur.com/rSjZ8.jpg" alt="enter image description here"></p>
  
  <p>The above image is the main workbook, which I would like to insert the macro into. In cell A1 you can see that there is a filename location - this is the location of the closed secondary workbook which I would like to pull the data from.</p>
  
  <p>I would like the macro to take the location which is present in cell A1, copy cells A1:J5000 in the closed workbook, and then paste these into this workbook starting in A7 (i.e. A7:J5007). The reason that the filename location is present in A1 is due to the fact that this will be changing; however I would like the macro always to take the location which is shown in A1 (e.g. if A1 were to change from '...\test00218_data.csv' to '...\test00001_data.csv' then I would like the macro to take the data from the new location, test00001). </p>
  
  <p>Since then I have written a macro which I believe would open up all the Sheets named "Raw Data x" and paste the required data into the appropriate areas of the primary sheet; the code is as follows:</p>
  
  <pre><code>Sub PullClosedData()
  
  Dim filePath As String
  
  For x = 1 To 1 Step 1
  
      filePath = Sheets("Raw Data " &amp; x).Cells(1, 1).Value
  
      Workbooks.Open Filename:=filePath
  
      Sheets("Raw Data 1").Range("A7:J2113").Value = ActiveWorkbook.ActiveSheet.Range("A1:J2107")
  Next x
  
  End Sub
  </code></pre>
  
  <p>When I run this however I get a Runtime error 9 (out of range). I believe this has something to do with the "ActiveWorkbook.ActiveSheet" part of the script, but am unsure how to re-write this and avoid the error.</p>