Adjust existing macro to pick up specific named worksheet and copy to named range

You could try this.
It assumes that the "Master" has been saved in the same folder.
Not tested so make sure to try with copies of your file(s)

Sub Maybe()
Dim file_Open As String, i As Long
With Application
   .DisplayAlerts = False
   .ScreenUpdating = False
    For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
    file_Open = Dir(ThisWorkbook.Path & "\" & Cells(i, 2).Value & ".xl*")
        Workbooks.Open ThisWorkbook.Path & "\" & file_Open
           With ActiveWorkbook.Sheets("ABC")
             UsedRange.Copy ThisWorkbook.Sheets(Cells(i, 2).Value).Cells(Rows.Count, 1).End(xlUp).Offset(1)
           End With
        Workbooks(file_Open).Close False
    file_Open = Dir
    Next i
    .DisplayAlerts = True
    .ScreenUpdating = True
  End With
End Sub

Hi jolivanes,

Thanks for your code. I'm trying my best to understand it. The macro as I currently have it is housed in the ThisWorkbook. It then prompts the user to select the Summary file which becomes Workbooks(2). The macro then prompts the user to identify where the actual external files are located. The results in these external files are supposed to be copied into Workbooks(2). I'm not certain I see how that works in your code? Can you clarify?

I thought I answered as per your explanation. I did not look at your code as it does not do what you want it to do.
I'll have a look later when I have some time again.
Sorry about that.

So here's what I have now. Its close but not quite doing what I need.

What its not doing right:

1. Its only pulling opening the first workbook in the folder rather than looking for the file indicated in myFile.
2. its also not looping through the list of cells in I
3. Its not renaming the copied worksheet to the i value

What it is doing right:

1. Copying the correct worksheet from the newly opened workbook to the correct position in Workbooks(2)

Option Explicit
Sub Update_Summary()
Dim wb As Workbook
Dim ws As Worksheet
Dim myPath As String, myFile As String, myExtension As String
Dim FldrPicker As FileDialog
Dim FilePicker As FileDialog
Dim i As Long

  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.DisplayAlerts = False
  Application.Calculation = xlCalculationManual
  
    MsgBox ("Please select Summary File")

    myFile = Application.GetOpenFilename

    Workbooks.Open (myFile), UpdateLinks:=0
    
    Workbooks(1).Worksheets("Data").Range("D2").Value = ActiveWorkbook.Path
    
    Workbooks(1).Worksheets("Data").Range("D3").Value = ActiveWorkbook.Name

  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

  myExtension = "*.xl*"

  Do While myFile <> ""
      For i = 2 To Workbooks(1).Worksheets("Hier").Cells(Rows.Count, 5).End(xlUp).Row
      myFile = Dir(myPath & Cells(i, 5).Value & myExtension)
      MsgBox Workbooks(1).Worksheets("Hierarchy").Cells(Rows.Count, 5).End(xlUp).Value
      
      Set wb = Workbooks.Open(Filename:=myPath & myFile)

      wb.Worksheets("ABC").Copy after:=Workbooks(2).Sheets("BEN")
      ActiveSheet.Name = i

      On Error GoTo 0
      wb.Close savechanges:=False
      Next i

      
      DoEvents

      myFile = Dir
  Loop

  MsgBox "Task Complete!"

ResetSettings:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Any ideas?

Thanks!

I don't get your logic.
Why would you "run through each workbook in a folder" to see if it's name is the same as in one of the cells in a range. (See Post #1, #1 and #2)
Why not use the cells in the range to open that particular workbook?
I can't make head or tails out of the code you have in Post #5 either.

Do you want to open all the files in a range in your active (call it Master for now) workbook and copy the used range from a sheet named "ABC" in that opened workbook into your Master?
I am terrible reading/understanding someone's code that does not work.
Either explain properly what you want to achieve or maybe someone else understands the code.

I tried to get your code from Post #5 to work but wasn't able to.
Could you go through the motions and explain what should, or what you want to, happen at each line.

Try this. It might be a start of what you're after.
You have to change all the references like sheet names ans cells ranges as required.

Sub Update_Summary()
Dim wb1 As Workbook, wbArr, myPath As String, wb2 As String, i As Long, file_Open As String

Application.ScreenUpdating = False

Set wb1 = ThisWorkbook    '<----- The workbook with this code in it

wbArr = Range("I2:I" & Cells(Rows.Count, "I").End(xlUp).Row).Value    '<----- Names of the files to be opened.

myPath = ChooseFolder() & "\"

MsgBox ("Please select Summary File")
    
wb2 = Application.GetOpenFilename()
Workbooks.Open (wb2), UpdateLinks:=0
wb2 = Dir(wb2, vbDirectory)    '<----- Need Workbook name without the path

For i = LBound(wbArr) To UBound(wbArr)    '<----- Go through all the workbooks one by one

file_Open = Dir(myPath & wbArr(i, 1) & ".xl*")
    If Dir(myPath & wbArr(i, 1) & ".xl*") <> "" Then
        Workbooks.Open myPath & file_Open
            ActiveWorkbook.Sheets("Sheet1").Copy After:=Workbooks(wb2).Sheets(Sheets.Count)    '<----- Here it copies Sheet1. Needs changing. Also change Sheets.Count
        With ActiveSheet
            .Name = wbArr(i, 1) & " " & i    '<----- Might want to change the naming also
        End With

    Workbooks(file_Open).Close False
    file_Open = Dir

    End If

Next i

Application.ScreenUpdating = True
End Sub

Function ChooseFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String

    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = "C:\"
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With

NextCode:
    ChooseFolder = sItem
    Set fldr = Nothing
End Function