Hi,

I'm needing a macro that will search through a specific file location, and return the desired cell ranges of ALL SPREADSHEETS saved in this location. I then want to sort alphabetically by the first column.

Ultimately, I'm trying to create a summary file of these spreadsheets which relate to individual projects. Here is a little more information below:


1) The spreadsheet I'm working on and want to build the macro in will be saved: I:\Accounts\2018\Management Accounts and the spreadsheet name is 'Summary Sheet'. The tab name is 'TAB1'.

2) The spreadsheets I'm wanting to loop through will each have a different filename (all starting with a 5 digit project code) and will be saved here I:\Accounts\2018\Financial Reporting\BRD\NewRev

3) The tab name of each of the loop through spreadsheets (saved with a 5 digit project code) will be C&C

4) These loop through spreadsheets (saved with a 5 digit project code) are saved as xlsm.

5) From each of these loop through spreadsheets (saved with a 5 digit project code), I need to extract the data from F24:AK24 and F29:AK29

6)The data (F24:AK24 and F29:AK29) from each of the loop through spreadsheets (saved with a 5 digit project code) needs to be extracted to the Summary Sheet file in TAB1 as described in 1). At present this can be extracted to cell A1 until I develop it a little further. I'm assuming it'll just be a case of updating the code to the desired range?

7) Once extracted to the Summary Sheet spreadsheet - I then need to sort all returned data alphabetically by column A (this was column F in the loop through spreadsheets)

8) As an FYI the loop through spreadsheets (saved with a 5 digit project code) contain the following code which is a open file / copy data / paste data / close file. I'm assuming this will not interfer or have any impact on the macro I'm trying to build here?

PHP Code: 
Private Sub Workbook_Open()
    Application.WindowState = xlMaximized
    Call MonkeyNuts
End Sub
Sub MonkeyNuts()

    Dim wkb As Workbook: Set wkb = Workbooks.Open("I:\Accounts\2018\Financial Reporting\BRD\NewRev\Data.xlsx", True, True)
    Dim LR  As Long
    
    With wkb
        'Change the sheetname "Time" to name of second sheet. Or use Sheets(2), assuming the index number of the sheet relates to the second sheet shown in your spreadsheet
        LR = .Sheets("Time").Cells(.Sheets("Time").Rows.Count, 1).End(xlUp).Row
        ThisWorkbook.Sheets("Time").Range("B1:Y30000").Value = .Sheets("Time").Range("A1:X30000").Value
        
        LR = .Sheets("Jobs").Cells(.Sheets("Jobs").Rows.Count, 1).End(xlUp).Row
        ThisWorkbook.Sheets("Jobs").Range("A1:Z2000").Value = .Sheets("Jobs").Range("A1:Z2000").Value
        .Close False
    End With
    
    Sheets("Time").Select


    Set wkb = Nothing
End Sub 

If anyone can help, I would be extremely grateful to you.

Thanks in advance,
Ryan