Open file dynamically and copy data using advanced filter

Hi,

I am newbie to excel vba and need your vba help.

My question:

Let's say I have an Output file open and I am on a particular sheet (active sheet) and when I run the macro it should give an option to open a particular file (get open filename) and after selecting the file it should filter the data from the selected file (in this case it is raw file and then use advance filter and copy and paste from raw file to output file which is open)

Note:
There is only one sheet in Raw file from where macro should pull the data and paste it into Output file (active sheet)

Please help.

Thanks!

whats the criteria for your filter?

Here is a code I made for you. I am asuming that the criteria to filter are the names in column L in your sheet

Sub ImportFilteredData()

Dim wbOriginal As Workbook
Dim fileToOpen As String
Dim rng As Range
Dim lrow As Long
Dim lrowFilter As Long
Dim lrowRaw As Long

application.screenupdating=false
Set wbOriginal = ActiveWorkbook
'find the last row of data

With wbOriginal.Sheets("Sheet1")
    lrow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    lrowFilter = .Cells(Rows.Count, 12).End(xlUp).Row
    
    'open the new file
    fileToOpen = Application.GetOpenFilename
    Workbooks.OpenText fileName:=fileToOpen
      
    For Each rng In .Range("L2:L" & lrowFilter)
        lrowRaw = ActiveWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
        'filter the data
        ActiveSheet.Range("$A$1:$F$" & lrowRaw).AutoFilter Field:=1, Criteria1:=rng.Value
        
        'copy the data
        ActiveWorkbook.Sheets(1).Range("A2:F" & Cells(Rows.Count, 1).End(xlUp).Row).Copy _
        Destination:=wbOriginal.Sheets(1).Cells(lrow, 1)
        lrow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        ActiveSheet.Range("$A$1:$F$" & lrowRaw).AutoFilter Field:=1
    Next
End With

'close the data workbook
Application.DisplayAlerts = False
    ActiveWorkbook.Close
Application.DisplayAlerts = True
application.screenupdating=true

End Sub

Thanks

Thank you so much fredlo2010; works brilliantly