AdvancedFilter Copy And Paste in Diferrent Workbooks ?

[rodrigo_m_almeida]

Could anyone help ?

How can I do this...
Trying this way

Problem with advancedfilter !

Public Sub SEARCH()

    ' Optimize
        Application.Visible = False: Application.ScreenUpdating = False: Application.DisplayAlerts = False
    ' Declare Variables
        Dim xPathName, xFileName As String, xOldWB, xNewWB As Workbook
    ' Browse Folder
        Set FileDialog = Application.FileDialog(msoFileDialogFolderPicker)
        FileDialog.Title = "Select Folder :"
        If FileDialog.Show = -1 Then xPathName = FileDialog.SelectedItems(1) Else Exit Sub
        If Right(xPathName, 1) <> "\" Then xPathName = xPathName + "\"
    ' Open File 02
        Workbooks.Add
        Set xNewWB = ActiveWorkbook
    ' Search File
        xFileName = Dir(xPathName & "FINAL*.xlsx")
    ' Open File 01
        Do While xFileName <> ""
 
            Workbooks.Open Filename:=xPathName & xFileName
            Set xOldWB = ActiveWorkbook
      
            ' Copy And Paste (Unique) for Other Workbook / >>> ERROR 1004 <<<
            xOldWB.Sheets(1).Columns("A:D").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=xNewWB.Sheets(1).Columns("A:D"), Unique:=True
      
    ' Save File 01

    ' Close File 01
            xOldWB.Close
    ' Next File
            xFileName = Dir
        Loop
    ' Save File 02
        xNewWB.SaveAs Filename:=xPathName & "ABC123", FileFormat:=xlWorkbookDefault
    ' Close File 02
        xNewWB.Close
    ' Optimize
        Application.Visible = True: Application.ScreenUpdating = True: Application.DisplayAlerts = True

 

[sijpie]

Hi Rodrigo,
As a first comment I would advise you to use sub and function names that are NOT the same as inbuilt Excel function names (SEARCH()).

I will have to go through the code in depth to see what you are trying to achieve.