Use a macro to import

Simon_Panton 

To import data from multiple source files into a single sheet using a macro in Excel, you can use the following code as a starting point:

Vba Code (untested):

Sub ImportDataFromFiles()
    Dim SourceFolder As String
    Dim FileName As String
    Dim DestinationSheet As Worksheet
    Dim LastRow As Long
    Dim CurrentRow As Long
    
    ' Set the folder path where the source files are located
    SourceFolder = "C:\Path\to\Source\Folder\" ' Replace with your actual folder path
    
    ' Set the destination sheet where the data will be imported
    Set DestinationSheet = ThisWorkbook.Sheets("DestinationSheetName") ' Replace "DestinationSheetName" with the actual sheet name
    
    ' Clear previous data in the destination sheet
    DestinationSheet.UsedRange.Clear
    
    ' Loop through each file in the source folder
    FileName = Dir(SourceFolder & "*.xlsx") ' Modify the file extension if necessary
    
    CurrentRow = 1 ' Starting row in the destination sheet
    
    Do While FileName <> ""
        ' Open the source file
        Workbooks.Open SourceFolder & FileName
        
        ' Copy data from source file to destination sheet
        With Workbooks(FileName).Sheets(1) ' Modify the sheet index if necessary
            LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
            
            ' Copy data from source cells D1 and D2 to destination columns A and B
            DestinationSheet.Range("A" & CurrentRow & ":A" & CurrentRow + LastRow - 1).Value = .Range("D1").Value
            DestinationSheet.Range("B" & CurrentRow & ":B" & CurrentRow + LastRow - 1).Value = .Range("D2").Value
        End With
        
        ' Close the source file without saving
        Workbooks(FileName).Close SaveChanges:=False
        
        ' Move to the next row in the destination sheet
        CurrentRow = CurrentRow + LastRow
        FileName = Dir
    Loop
End Sub

Before running the macro, you need to update the SourceFolder variable with the actual folder path where your source files are located. Also, update the DestinationSheet variable with the name of the destination sheet where you want to import the data.

This macro loops through each file in the source folder, opens the file, copies the data from cells D1 and D2 in the first sheet, and pastes it into columns A and B of the destination sheet.

The data is appended row by row, so each file's data will be in a separate block in the destination sheet.

Make sure to modify the file extension and the sheet index in the code if your source files have a different file extension or if the data is located in a different sheet within the source files.

Before running the macro, save your workbook and test it with a few sample files to ensure it works as expected.

To run the macro in Excel for Mac, follow these steps:

1. Open your Excel workbook.
2. Press Option + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
3. In the VBA editor, insert a new module by clicking on "Insert" and selecting "Module."
4. Copy and paste the code into the new module.
5. Modify the SourceFolder and DestinationSheet variables with the appropriate folder path and sheet name.
6. Close the VBA editor.
7. Press Option + F8 to open the "Macro" dialog box.
8. Select the "ImportDataFromFiles" macro from the list and click "Run."

The macro should now execute and import the data from the source files into the designated sheet in your Excel workbook.

Please note that if your source files have a different file extension or the data is located in a different sheet within the files, you may need to modify the code accordingly.

To be sure that the code is behaving the way you want, it would be helpful to have the file (without any sensitive data) to run tests on rather than reconstructing one yourself. Information such as Excel version, file extension, operating system, storage medium would be of additional help. The text, steps and functions were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.