Arrays Dictionary for loop... Most efficient way to copy files from list1 and paste them on sheet name from list2

[andnand]

How can I open file1 from list1, copy sheet1 from file1, paste it on sheetname1 from list2, close file1 and go to the next.... for any amount of files, variable until the named range is done.

Sheets("Data").Range("E2:E30") is list1, list of files {C:\Documents\robertheinz.xlsx, C:\Documents\old\cindyklems.csv, C:\Documents\one\juanwhite.csv....}

Sheets("Data").Range("F2:F30") is list2, list of sheet names already in workbook {rob, cindy, juan......}

if sheetname doesnt exist then create it.

Macro in activeworkbook, all sheets in activeworkbook, files are opened to copy data and then closed

Thank you!!

(Is any form of arrays, dictionary or collections better than a regular for loop)

    Sub impfiles()
    Dim wb_CSV, wb_CSV2 As Workbook
    Dim fpath  As String
    Dim path As Variant
    Dim rng As Range
    Dim shname As String

    Dim rg, rg2, rg3 As Range
    Dim lastrow, lastrow2, lastrow3, lastrow4 As Integer

    Set wb_CSV2 = ThisWorkbook
    Set rng = D_files.Range("FPATHS")
    
    For Each path In rng
    
    fpath = path.Value
    shname = Application.WorksheetFunction.Index(D_files.Range("F2:F30"), Application.WorksheetFunction.Match(path.Value, D_files.Range("E2:E30"), 0))
    Sheets(shname).UsedRange.ClearContents

    Set wb_CSV = Workbooks.Open(fpath)
    wb_CSV.Sheet1.Cells.Copy Destination:=wb_CSV2.Sheets(shname).Range("A1")

    Application.CutCopyMode = False
    wb_CSV.Close
    Set wb_CSV = Nothing
    
    Next
    End Sub