Pulling data from a closed workbook macro

Question

I've been having issues concerning pulling data from a closed workbook by using a macro.

enter image description here

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.

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).

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:

Sub PullClosedData()

Dim filePath As String

For x = 1 To 1 Step 1

    filePath = Sheets("Raw Data " & 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

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.

one angry researcher one angry researcher · Accepted Answer · 2014-07-25T11:29:08

First off, do not stick the path into a cell that you plan on overwriting. Instead, create a separate sheet containing vital input parameters (see example below; I'm calling that sheet "System").

enter image description here

The code below pulls data from the workbooks "Raw Data 1" to "Raw Data 3" from the source book.

Make sure you properly define your workbooks in variables (TargetWb and SourceWb).
When referencing a worksheet, always specify what workbook it is located in when using multiple workbooks (e.g. TargetWb.ActiveWorksheet, not just ActiveWorksheet)

.

Sub PullClosedData()

    Dim filePath As String
    Dim SourceWb As Workbook
    Dim TargetWb As Workbook

    Set TargetWb = ActiveWorkbook

    filePath = TargetWb.Sheets("System").Range("A1").Value
    Set SourceWb = Workbooks.Open(filePath)

    For i = 1 To 3
        SourceWb.Sheets("Raw Data " & i).Range("A1:J5000").Copy Destination:=TargetWb.Sheets("Raw Data " & i).Range("A1:J5000")
    Next i

    SourceWb.Close

    MsgBox "All done!"

End Sub

Pulling data from a closed workbook macro

1 Answers