Several filters with VBA

Question

Guys good afternoon

I need to perform a filter on a worksheet column, collect a certain value and delete the rest of the same column. I have this code but it only works for 1 tab. I need a Cod for more than 10 tabs... can someone help me? follows the Cod

'execute Sub-file_01()

Dim lLin As Long

'Way to flood the arch that will be generated

fname1 = (sCaminho & "\" & Format(data, "yyyy") & _
"\" & Format(data, "mmmm")) & "\" & Format(data, "dd") & _
"\" & "nome do arquvio"

' selection of flaps

Worksheets(Array("Plan1")).Copy

' Filter Plan1

Application.ScreenUpdating = False

'Altere o nome da planilha abaixo:
With Sheets("Plan1")
    For lLin = .Cells(.Rows.Count, "E").End(xlUp).Row To 2 Step -1
        If .Cells(lLin, "E") = "Cell 02" Then .Rows(lLin).Delete
        If .Cells(lLin, "E") = "Cell 03" Then .Rows(lLin).Delete

        'Desafoga os processos pendentes do Windows a cada 100 linhas iteradas:
        If lLin Mod 100 = 0 Then DoEvents
    Next lLin
    End With

Application.ScreenUpdating = True

Answer 1

Apparently, if this code works for one tab, just iterate on several, which can be done like this:

Dim Plan as Worksheet

For each Plan in ActiveWorkbook.Sheets ' Pressupondo que o arquivo com as planilhas é o que está ativo quando esta função for chamada; se não for, fazer referência ao arquivo de excel adequado.
    'Aqui você cola seu código que funciona para uma planilha,
    ' só que usando a variável Plan para as referências.
    ' Por exemplo:
    Plan.copy ' Em vez de Worksheets(Array("Plan1")).Copy
    With Plan ' Em vez de With Sheets("Plan1")
    'Pelo código acima, acho que só as duas linhas acima precisam
    ' de adaptação, as outras não mudam nada.

Next Plan

Just one question: you didn’t put all your code here, right? I ask this for two reasons:

1) You copy the spreadsheet without pasting anywhere, so that the instruction Copy was lost/useless;

2) You delete several lines from the Plan1 spreadsheet from the original file, but do not compile the result elsewhere. If you run this code by iterating all worksheets in the form described above, you will end up with the original file without the deleted lines but still divided into different worksheets. To unify them in a single spreadsheet, you could copy the contents of each worksheet (property UsedRange of each spreadsheet) and paste everything into one spreadsheet.

EDIT ABOUT THE PROPERTY USEDRANGE: UsedRange is a property of the Spreadsheet object (WorkSheet) that returns a range with the area in use. Therefore, to know the address of the range in use in the spreadsheet, you could use Plan.UsedRange.Address, that returns a string with the address in use in the Plan spreadsheet. To find out the last line, you can use Plan.Cells.SpecialCells(xlCellTypeLastCell).Row. However, take care of the property UsedRange. It usually resets each time it is used, so it is not interesting to make multiple references to the Usedrange of a spreadsheet. If you need to use it often, it’s best to assign the Usedrange to a Range type variable and work with it, or search in other ways (gives a search or question here in Stackoverflow, because this whole Dit no longer has to do with the main subject of this question).