Auto VBA Filter - excel

Question

I need to make a filter with a certain value in a spreadsheet tab,after the filter is done, I need to copy, open a new document, and paste this statement.

I developed the code below but it’s not working.

follows the code

Sub filtrar()

Worksheets(Array("Planilha3", "Planilha2", "Planilha3",)).Copy

ActiveSheet.Range("Planilha3").AutoFilter Field:=Range("E1:E1048576").Column, Criteria1:="Cell 01"


With ActiveWorkbook

    ActiveWorkbook.SaveAs Filename:=fname1, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    ThisWorkbook.Save
    ThisWorkbook.Close



End With
End Sub

Answer 1

Code

Sub filtrar()
    Dim ws3 As Worksheet
    Dim UltimaLinhaE As Long
    Worksheets(Array("Planilha1", "Planilha2", "Planilha3")).Copy
    Set ws3 = ActiveWorkbook.Worksheets("Planilha3")
    With ws3
        'Limpa os Autofiltros da Planilha para evitar erros
        If .FilterMode Then
            .ShowAllData
        End If
        'Última Linhada colunaE
        UltimaLinhaE = .Cells(.Rows.Count, "E").End(xlUp).Row
        'Autofiltro
        .Range("E1:E" & UltimaLinhaE).AutoFilter Field:=1, Criteria1:="Cell 01"
    End With

    With ActiveWorkbook
        ActiveWorkbook.SaveAs Filename:=fname1, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        ThisWorkbook.Save
        ThisWorkbook.Close
    End With
End Sub

Explanation

The Autofilter Method was being misused.

The method is Range.Autofilter, so a range needs to be inserted.

Thus: .Range("E1:E" & UltimaLinhaE)

Obs.: The variable fname1 is not being specified in this code. And if what you want is to filter before uploading to the new Excel file, use .SpecialCells(xlCellTypeVisible) and create a new temporary sheet to be copied to the new file.