Macro - How to retrieve data from other - closed - workbooks?

[kit99]

In Workbook "wbInTo" I have three different sheets, "In1", "In2" and "In3".
To each of these sheets I want to retrieve all the data from Sheet1 in 3 other - closed - Workbooks, named "wbFrom1", "wbFrom2" and "wbFrom3".

Data from "wbFrom1, sheet1" into "wbInTo, In1", data from "wbFrom2, sheet1" into "wbInTo, In2", etc.


Any suggestions for macro?

 

[JLGWhiz]

In Workbook "wbInTo" I have three different sheets, "In1", "In2" and "In3".
To each of these sheets I want to retrieve all the data from Sheet1 in 3 other - closed - Workbooks, named "wbFrom1", "wbFrom2" and "wbFrom3".

Data from "wbFrom1, sheet1" into "wbInTo, In1", data from "wbFrom2, sheet1" into "wbInTo, In2", etc.


Any suggestions for macro?

If you are using VBA, is there any reason why you would not want to have it open the workbook, retrieve the data, then close the workbook?

P.S. The reason no one was responding is that there is insufficient information to in the post to do what you want.

 

[kit99]

If you are using VBA, is there any reason why you would not want to have it open the workbook, retrieve the data, then close the workbook?

While running a macro from "wbInTo", it's no problem for me if the macro open "wbFrom1", copy all data in "sheet1", close down "wbFrom, and then paste data into "wbInTo", sheet "In1". Then continues to open "wbFrom2", and so on.
Can this be done?

 

[kit99]

By "closed" I ment that the "From"-wb's are not activated/opened at the moment of running the macro. But there are no restrictions to them. They are laying in a subfolder to the "wbInTo" wb, and the macro might use any means necessary to pull data out of them.

 

[JLGWhiz]

This code was tested in the following manner.
1. Destination Workbook was used to host the procedure.
2. Three workbooks were created in the same directory as the host workbook, named wbFrom1, wbFrom2 and wbFrom3
3. Three sheets were named In1, In2 and In3 in the host workbook.
4. Miscellaneous data was entered on sheet 1 of each wbFrom workbook.
5. The In# sheets in the host workbook were blank before running the macro.
6. The procedure ran as expected and copied the data from each of the three wbFrom workbooks without error.

Sub getdataFromwb()
Dim wb As Workbook, sh As Worksheet, fPath As String, fName As String
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    For i = 1 To 3
        On Error Resume Next
        Set wb = Workbooks.Open(fPath & "wbFrom" & i & ".xlsx") 'File extension assumed standard workbook
        wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("In" & i).Range("A1")
        wb.Close False
        On Error GoTo 0
    Next
End Sub

 

[kit99]

Fantastic, yet again!
Runs smooth, when set up like the example!
Thanks a lot!

Now I've gotta try to implement this to my real workbooks. In that world the "From's" ain't named 1, 2 and 3 (totally different names on all 3). And the wbInto and the wbFrom are not located in the same folder (wbFroms are located in a subfolder to wbInTo). I'll give it a try! But feel free to give any advice to a vba-newbie on lines in the code I would need to change...

 

[JLGWhiz]

Fantastic, yet again!
Runs smooth, when set up like the example!
Thanks a lot!

Now I've gotta try to implement this to my real workbooks. In that world the "From's" ain't named 1, 2 and 3 (totally different names on all 3). And the wbInto and the wbFrom are not located in the same folder (wbFroms are located in a subfolder to wbInTo). I'll give it a try! But feel free to give any advice to a vba-newbie on lines in the code I would need to change...

List the workbook names in a column somewhere and use the row numbers as the 'i' variable values.
Assumen they are listed on sheet 2 of the host workbook in column A, cells 2 - 4.

For i = 2 To 4
    Set wb = Workbooks.Open(Sheets(2).Range("A" & i).Value & ".xlsx") 'If you have the extension includied in the list, then omit it here.
    'etc.
Next