Use a macro to import

Simon_Panton 

It seems there are a few issues with the modified code you provided. Here are the corrections and clarifications:

1. Subscript out of range error: The "Subscript out of range" error occurs when the script is trying to access a workbook or worksheet that doesn't exist. In your code, you are trying to access a sheet named "sample sites2.xlsx," which is incorrect. The sheet should be referred to by its name, not the name of the file it belongs to. Modify the line to refer to the sheet name:

Vba Code:

Set DestinationSheet = ThisWorkbook.Sheets("SheetName") ' Replace "SheetName" with the actual name of the destination sheet in your workbook

1. acro dialog box in Excel for Mac: On Mac, the shortcut to open the VBA editor is Option + F11. However, if you're unable to open the VBA editor using the shortcut, you can try accessing it through the "Developer" tab. To enable the "Developer" tab in Excel for Mac, go to Excel Preferences -> Ribbon & Toolbar, and check the box next to "Developer" under the "Customize the Ribbon" section. Then, you can access the VBA editor by clicking on the "Developer" tab and selecting "Visual Basic."
2. Destination sheet and file: The DestinationSheet variable refers to the destination worksheet within the same workbook where you are running the macro. Make sure the sheet name is correct. The workbook where the macro is saved does not need to be macro-enabled, but it should be open when you run the macro.

Here is the corrected version of the code:

Vba Code:

Sub ImportDataFromFiles()
    Dim SourceFolder As String
    Dim FileName As String
    Dim DestinationSheet As Worksheet
    Dim LastRow As Long
    Dim CurrentRow As Long

    SourceFolder = "/Users/simonpanton/Library/CloudStorage/OneDrive-Personal/MSc/MSC Project/Raw data/Freshwater/"

    Set DestinationSheet = ThisWorkbook.Sheets("SheetName") ' Replace "SheetName" with the actual name of the destination sheet in your workbook
    DestinationSheet.UsedRange.Clear

    FileName = Dir(SourceFolder & "*.xlsx") ' Modify the file extension if necessary
    CurrentRow = 1 ' Starting row in the destination sheet

    Do While FileName <> ""
        Workbooks.Open SourceFolder & FileName

        With Workbooks(FileName).Sheets(1) ' Modify the sheet index if necessary
            LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row

            DestinationSheet.Range("A" & CurrentRow & ":A" & CurrentRow + LastRow - 1).Value = .Range("F1").Value
            DestinationSheet.Range("B" & CurrentRow & ":B" & CurrentRow + LastRow - 1).Value = .Range("F2").Value
            DestinationSheet.Range("C" & CurrentRow & ":C" & CurrentRow + LastRow - 1).Value = .Range("B2").Value
            DestinationSheet.Range("D" & CurrentRow & ":D" & CurrentRow + LastRow - 1).Value = .Range("B4").Value
        End With

        Workbooks(FileName).Close SaveChanges:=False

        CurrentRow = CurrentRow + LastRow
        FileName = Dir
    Loop
End Sub

Make sure to replace "SheetName" with the actual name of the destination sheet in your workbook. Also, ensure that the source folder and file paths are accurate.

Once you have corrected the code, run the macro again, and it should import the data from the specified files into the destination sheet. The text, steps and code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.