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

rub

New Member
Joined
May 5, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,811
With the concatenated cells in column E, try this macro.
VBA Code:
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
 
Solution

rub

New Member
Joined
May 5, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
With the concatenated cells in column E, try this macro.
VBA Code:
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,969
Messages
5,656,152
Members
418,283
Latest member
Venkateswara Rao

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top