Extract data from multiple workbooks in same folder

MPFraser7

New Member
Joined
Dec 14, 2016
Messages
34
I have multiple excel files in the following folder "C:\Users\frasmar\Desktop\Test" (all .xls files) where I want to extract data from the sheet named "Tombstone - DonnéesDeBase". The data that I want to extract is range "M4:M13". I would like to copy that range and paste it in another workbook (the one with the macro, named "extract macro.xlsm") which isn't in the same folder. For the pasting part, I assume I need something along the lines of search for the first blank cell in column A and paste.

The way I see it is that it would open the first workbook in the folder, copy the range in the specified sheet, paste it in column A of the workbook with the macro, close the first workbook, open the second one, so on so forth.

Any help on this? Thanks in advance.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,624
Try this:
Code:
Public Sub Copy_Values_From_Workbooks()

    Dim folderPath As String
    Dim fileName As String
    Dim destCell As Range, r As Long
    Dim fromWorkbook As Workbook
    
    'Folder containing the workbooks
    
    folderPath = "C:\Users\frasmar\Desktop\Test\"
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    With ThisWorkbook.ActiveSheet
        Set destCell = .Cells(.Rows.Count, "A").End(xlUp)
        If Not IsEmpty(destCell.Value) Then Set destCell = destCell.Offset(1)
    End With
    
    'Open file names matching *.xls
    
    fileName = Dir(folderPath & "*.xls")
    r = 0
    Do While fileName <> vbNullString
        Set fromWorkbook = Workbooks.Open(folderPath & fileName)
        destCell.Offset(r).Resize(10, 1).Value = fromWorkbook.Worksheets("Tombstone - DonnéesDeBase").Range("M4:M13").Value
        r = r + 10
        fromWorkbook.Close savechanges:=False
        DoEvents
        
        'Get next file name
        
        fileName = Dir
    Loop
    
    MsgBox "Finished"
    
End Sub
 

jerk

New Member
Joined
Jul 28, 2020
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2016
John can you help me with your code to modify to put every file at one row ?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,624
It's not clear what you want. If you want the M4:M13 cell values from each workbook to be transposed and put in multiple columns across each row, then replace:
VBA Code:
       destCell.Offset(r).Resize(10, 1).Value = fromWorkbook.Worksheets("Tombstone - DonnéesDeBase").Range("M4:M13").Value
       r = r + 10
with
VBA Code:
       destCell.Offset(r).Resize(1, 10).Value = Application.Transpose(fromWorkbook.Worksheets("Tombstone - DonnéesDeBase").Range("M4:M13").Value)
       r = r + 1
That's untested though.
 

jerk

New Member
Joined
Jul 28, 2020
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2016
Thanks because you are try to help me...
Problem 1.
i need to get every file at one collum... file one in A1.... File two in B1.... etc
Problem 2.
and i need another thing for another fle ... in this cod i need to ignore empty space ... if i in a file i have text just in M4 i need to copy just M4 or if i have M4 M5 and M6 i need to copy just that
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,624
Please start a new thread because now your question is very different to the OP.

Include a precise description of your request and use XL2BB if you think it will help show your data layout and desired outcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,923
Members
414,416
Latest member
Nobu

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