Macro for pulling data from a closed workbook

[pmcgrath92]

Hi all,

I'm new to the forum and new to VB, and was wondering if there was anyone out there who could lend a hand with a problem I'm having concerning pulling data from a closed workbook by using a macro. I have tried Google to resolve the issue, but am only getting very basic results which do not apply to the situation I have (or, rather, I perhaps don't know how to edit this code in order to work in the way which I would like it to). Apologies in advance if I come across as a complete noob, and any help/comments would be greatly appreciated!

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). I'm not sure if this is possible to even do, and as mentioned above any help on the matter would be extremely appreciated.

Paul

 

[NeonRedSharpie]

Sub macroForPullingClosedData()

    Dim filePath As String
    
    filePath = ThisWorkbook.Cells(1, 1).Value
    
    workbooks.Open fileName:=filePath
    
    ThisWorkbook.Range("A7:J5006").Value = ActiveWorkbook.Range("A1:J5000")

    ActiveWorkbook.Close
    
End Sub

I think this will work. Let me know.

 

[pmcgrath92]

Sub macroForPullingClosedData()

    Dim filePath As String
    
    filePath = ThisWorkbook.Cells(1, 1).Value
    
    workbooks.Open fileName:=filePath
    
    ThisWorkbook.Range("A7:J5006").Value = ActiveWorkbook.Range("A1:J5000")

    ActiveWorkbook.Close
    
End Sub

I think this will work. Let me know.

Thanks for the quick reply! I pasted in the macro which you suggested, but I am met with the following:

 

[NeonRedSharpie]

Sub macroForPullingClosedData()

    Dim filePath As String
    
    For x = 1 To 30 Step 1
    
        filePath = Sheets("Raw Data " & x).Cells(1, 1).Value
        
        workbooks.Open fileName:=filePath
        
        Sheets("Raw Data 1").Range("A7:J5006").Value = ActiveWorkbook.Range("A1:J5000")
    Next x
    
End Sub

I had a brain fart. I also just included some logic to loop through all your "Raw Data X" sheets. I put 30, but you can change that to whatever value you want. To test it, make it say "For x = 1 to 1 Step 1"

 

[pmcgrath92]

Sub macroForPullingClosedData()

    Dim filePath As String
    
    For x = 1 To 30 Step 1
    
        filePath = Sheets("Raw Data " & x).Cells(1, 1).Value
        
        workbooks.Open fileName:=filePath
        
        Sheets("Raw Data 1").Range("A7:J5006").Value = ActiveWorkbook.Range("A1:J5000")
    Next x
    
End Sub

I had a brain fart. I also just included some logic to loop through all your "Raw Data X" sheets. I put 30, but you can change that to whatever value you want. To test it, make it say "For x = 1 to 1 Step 1"

There are 20 'Raw Data' sheets, so I changed it to that (as you can see below), but am still being met with an error

 

[pmcgrath92]

Still having trouble with this if anyone is willing to help me out with this!

 

[NeonRedSharpie]

Sub macroForPullingClosedData()    Dim filePath As String
    For x = 1 To 30 Step 1
        filePath = Sheets("Raw Data " & x).Cells(1, 1).Value
        workbooks.Open fileName:=filePath
        Sheets("Raw Data 1").Range("A7:J5006").Value = ActiveWorkbook.ActiveSheet.Range("A1:J5000")
    Next x
End Sub

Try that? Sorry, it's hard to diagnose without being on the computer. I can't make a lot of sample sheets here at work.

 

[pmcgrath92]

I now get a different error:

 

[pmcgrath92]

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

I think the runtime error has something to do with

ActiveWorkbook.ActiveSheet

part of the code, this seems to be the root of many runtime errors it seems; but I'm not quite sure how to rewrite it in another way to avoid this - any ideas folks?