Apparently, if this code works for a tab, just iterate through 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: did not you put all your code here, right? I ask this for two reasons:
1) You copy the worksheet without pasting it, so that the Copy statement was lost / useless;
2) You exclude multiple rows from the Plan1 worksheet from the original file, but do not compile the result elsewhere. If you run this code by iterating all the worksheets in the way described above, it will end with the original file without the deleted rows but still divided into different worksheets. To unify them in one worksheet, you could copy the contents of each Worksheet ( UsedRange property of each worksheet) and paste it all into one worksheet.
PROPERTY EDIT USEDRANGE :
UsedRange is a property of the Spreadsheet object ( WorkSheet ) that returns a range with the area in use. So to know the address of the range in use in the worksheet, you could use Plan.UsedRange.Address , which returns you a string with the address in use in the Plan worksheet. To find the last line, you can use Plan.Cells.SpecialCells(xlCellTypeLastCell).Row .
However, be careful with UsedRange property. It wont reset every time it's used, so it's not interesting to make multiple references to the UsedRange from a spreadsheet. If you need to use it often, you'd better assign UsedRange to a variable of type Range and work with it, or search in other ways (give a lookup or ask here in StackOverflow, since this whole edit is no longer about main subject of that question).
