Loop through Excel spreadsheets

Question

I have the following code, and I would like it to work on 25 other sheets of the book and instead of repeating the code 25 times, is there a way for each sheet to make it a loop?

Can anyone help?

Sub DeleteEmptyRows() Dim ws As Worksheet Dim strSearch As String Dim lRow As Long strSearch = "ressort" Set ws = Sheets("01,02,03") With ws lRow = .Range("A" & .Rows.Count).End(xlUp).Row With .Range("A1:A" & lRow) .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With ActiveSheet.Range("$A$1:$P$65536").AutoFilter Field:=1 End With End Sub 

Answer 1

Wrap the processing code in a loop

 for each ws in thisworkbook.sheets ' do something on each worksheet next 

Example

 Sub DeleteEmptyRows() Dim ws As Worksheet Dim strSearch As String Dim lRow As Long strSearch = "ressort" For Each ws In ThisWorkbook.Sheets If (ws.Name <> "Sheet1") And (ws.Name <> "Sheet2") And (ws.Name <> "Sheet3") Then With ws lRow = .Range("A" & .Rows.Count).End(xlUp).Row With .Range("A1:A" & lRow) .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With ws.Range("$A$1:$P$65536").AutoFilter Field:=1 End With End If Next End Sub 

So, if the sheet names are Sheet1 or Sheet2 or Sheet3, they will be skipped.

Answer 2

Your code will need to be stored in the module, and not contained in a sheet. The following illustrates how the loop works:

 Sub test() Dim thisSheet As Worksheet For Each sheet In Sheets thisSheet.Cells(1, 1) = 1 Next End Sub