Copy data from closed workbooks in multiple folders into one master sheet

[rub]

Hi all,

I'm quite new no VBA, so trying to find my way here.
What I am trying to do is to copy a row of data from seperate Excel workbooks, located in different folders into one master sheet.

So what this looks like is that I have the following files in these locations:

C:\Users\username\Documents\Database\JohnJohnson\Data.xlsx
C:\Users\username\Documents\Database\PetePeterson\Data.xlsx
C:\Users\username\Documents\Database\MikeMicheals\Data.xlsx

and so on....

So what I am looking for is specifying these filepaths in my master sheet by concatenating cell contents to create this filepath so I know where to look for.
Let's say Sheet1 will have the file locations of where the code needs to search, so A1 will say 'C:\Users\username\Documents\Database\', B1 'John', C1 'Johnson', D1 '\Data.xlsx'.That concatenated will create: C:\Users\username\Documents\Database\JohnJohnson\Data.xlsx

Then I want my VBA code to look into that filepath, copy data from cell A1:F1, copy into Sheet2 of the mastersheet and then continue to the next file (PetePeterson), and copy this in the row underneath.
This will be a total of around 100 Data.xlsx files.

I hope this makes sense and you can help me with creating something for this!

Thanks!

 

[John_w]

With the concatenated cells in column E, try this macro.

Public Sub Copy_Values_From_Workbooks()

    Dim fileCells As Range, fileCell As Range
    Dim destCells As Range, r As Long
    Dim fromWorkbook As Workbook
    
    With ThisWorkbook
        With .Worksheets("Sheet1")
            Set fileCells = .Range(.Range("E1"), .Cells(.Rows.Count, "E").End(xlUp))
        End With
        Set destCells = .Worksheets("Sheet2").Range("A1:F1")
    End With
    
    Application.ScreenUpdating = False
    
    r = 0
    For Each fileCell In fileCells
        Set fromWorkbook = Workbooks.Open(fileCell.Value)
        destCells.Offset(r).Value = fromWorkbook.Worksheets(1).Range("A1:F1").Value
        fromWorkbook.Close SaveChanges:=False
        r = r + 1
        DoEvents
    Next
   
    Application.ScreenUpdating = True
    
    MsgBox "Finished"
    
End Sub

 

[rub]

With the concatenated cells in column E, try this macro.

Public Sub Copy_Values_From_Workbooks()

    Dim fileCells As Range, fileCell As Range
    Dim destCells As Range, r As Long
    Dim fromWorkbook As Workbook
   
    With ThisWorkbook
        With .Worksheets("Sheet1")
            Set fileCells = .Range(.Range("E1"), .Cells(.Rows.Count, "E").End(xlUp))
        End With
        Set destCells = .Worksheets("Sheet2").Range("A1:F1")
    End With
   
    Application.ScreenUpdating = False
   
    r = 0
    For Each fileCell In fileCells
        Set fromWorkbook = Workbooks.Open(fileCell.Value)
        destCells.Offset(r).Value = fromWorkbook.Worksheets(1).Range("A1:F1").Value
        fromWorkbook.Close SaveChanges:=False
        r = r + 1
        DoEvents
    Next
  
    Application.ScreenUpdating = True
   
    MsgBox "Finished"
   
End Sub

This is amazing, works perfect! Thanks a lot!!

 

[milab]

Hi-

I have a related question to OP.
How do I tweak the code to specify what tab of the file workbook I want to copy from ?
For example: Column A is the filepath, Column B is the tab name within the file to copy from.
I want the code to open each file and copy from cells A17:EY19 in "Data" tab, then go to the master workbook and paste into "Data_extract" tab on cell A8, continue to the next file and copy to the next empty row.

With the code posted above, I feel like I'm so close to tweaking it, just cant figure out how to get the code to copy from a specific tab.
Please help and thank you so much !!!

 

[John_w]

How do I tweak the code to specify what tab of the file workbook I want to copy from ?

Probably this (untested), but please start a new thread if this change doesn't achieve the required result because your request is a bit different.

destCells.Offset(r).Value = fromWorkbook.Worksheets(fileCell.Offset(,1).Value).Range("A17:EY19").Value