Extract data from multiple workbooks in same folder

[MPFraser7]

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.

 

[John_w]

Try this:

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]

John can you help me with your code to modify to put every file at one row ?

 

[John_w]

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:

       destCell.Offset(r).Resize(10, 1).Value = fromWorkbook.Worksheets("Tombstone - DonnéesDeBase").Range("M4:M13").Value
       r = r + 10

with

       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]

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]

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.