Loop through Excel Sheets
I have the following Code and I would like it to run in 25 other sheets of the Workbook and instead of repeating the code 25 times,for each sheet is there a way to make it loop?
Can someone assist?
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
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 now if the sheet names are Sheet1 or Sheet2 or Sheet3 they will be skipped.
Your code will need to be stored in a module, rather than being contained in a sheet. The following illusrates how the loop works:
Sub test()
Dim thisSheet As Worksheet
For Each sheet In Sheets
thisSheet.Cells(1, 1) = 1
Next
End Sub