VBA: Import folder contents, search for row/header copy file name to column

[hennesseeb]

Hello,

I've got a macro built that imports .csv files and copies a range of data into a destination workbook. What I need it to do is search the .csv files for certain header/row criteria and paste it into the destination workbook. See below for current code.

Each row that I need will start with 1,2,3...etc and span until column I. The number of rows will vary from file to file, so a search function is needed. The header names will stay the same throughout each file. Also, I will need the file name to populate column for each corresponding entry.

I would like this code so I don't have to manually open hundreds of files just to copy and paste data. I appreciate the help. I'm new here at Mr. Excel forums.

Sub ImportFolder()
    MsgBox "Select the folder you want to import."
    
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        If .Show = -1 Then
            folder = .SelectedItems(1)
        Else
            GoTo CANCEL
        End If
    End With
    Application.ScreenUpdating = False
    Dim StrFile As String
    StrFile = Dir(folder & "\*.csv")
    Do While Len(StrFile) > 0
        fandp = folder & "\" & StrFile
        
        Workbooks.Open Filename:=fandp
        Range("A8:I10").Select
        Selection.Copy
        Workbooks("File Import.xlsm").Activate
        Rows("2:2").Select
        Selection.Insert Shift:=xlDown
        Application.CutCopyMode = False
        c = Application.CountA(Sheets("Data").Range("A:A"))
        ActiveSheet.Range("$A$1:$I$" & c).RemoveDuplicates Columns:=1, Header:=xlYes
        
        Windows(StrFile).Close (False)
        StrFile = Dir
        Start = ""