Auto Filter VBA - excel

Question

Auto Filter VBA - excel

Navigation

#1 by (1 votes)

1

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

I have developed the code below but it is not working.

follow 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

vba

asked by anonymous 22.05.2018 / 18:33

1 answer

Create bat to finish Service CONCAT of querys DELETE?

score 1 · Accepted Answer

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 used incorrectly .

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

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

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