Incorporación de bucle de hoja

Hola, tengo este código que solo se ejecuta en una sola hoja (hoja3) pero quiero que pase por otras hojas del libro de trabajo y ejecute este código. Intenté usar el para cada ciclo pero no parece ser compatible con este código. He buscado otros methods de bucle pero estoy realmente inseguro de cómo lo hago.

Aquí está el código

Sub DeleteCells() Dim rng As Range, rngError As Range, delRange As Range Dim i As Long, j As Long On Error Resume Next Set rng = Application.InputBox("Select cells To be deleted", Type:=8) On Error GoTo 0 If rng Is Nothing Then Exit Sub Else rng.Delete With Sheets("Sheet3") For i = 1 To 7 '<~~ Loop trough columns A to G '~~> Check if that column has any errors On Error Resume Next Set rngError = .Columns(i).SpecialCells(xlCellTypeFormulas, xlErrors) On Error GoTo 0 If Not rngError Is Nothing Then For j = 1 To 100 '<~~ Loop Through rows 1 to 100 If .Cells(j, i).Text = "#REF!" Then '~~> Store The range to be deleted If delRange Is Nothing Then Set delRange = .Columns(i) Exit For Else Set delRange = Union(delRange, .Columns(i)) End If End If Next End If Next End With '~~> Delete the range in one go If Not delRange Is Nothing Then delRange.Delete End Sub 

Por lo general, puede recorrer las hojas utilizando su número de índice, o el mencionado para cada … Por lo tanto, si agrega a su código, esto significaría lo siguiente:

 Sub DeleteCells() Dim rng As Range, rngError As Range, delRange As Range Dim i As Long, j As Long, k as long Dim wks as Worksheet On Error Resume Next Set rng = Application.InputBox("Select cells To be deleted", Type:=8) On Error GoTo 0 If rng Is Nothing Then Exit Sub Else rng.Delete for k = 1 to thisworkbook.worksheets.count 'runs through all worksheets set wks=thisworkbook.worksheets(k) With wks For i = 1 To 7 '<~~ Loop trough columns A to G '~~> Check if that column has any errors On Error Resume Next Set rngError = .Columns(i).SpecialCells(xlCellTypeFormulas, xlErrors) On Error GoTo 0 If Not rngError Is Nothing Then For j = 1 To 100 '<~~ Loop Through rows 1 to 100 If .Cells(j, i).Text = "#REF!" Then '~~> Store The range to be deleted If delRange Is Nothing Then Set delRange = .Columns(i) Exit For Else Set delRange = Union(delRange, .Columns(i)) End If End If Next j End If Next i End With next k '~~> Delete the range in one go If Not delRange Is Nothing Then delRange.Delete End Sub 

Por lo general, también es mejor nombrar el "próximo", porque tiene una mejor visión general que para … el siguiente ciclo está cerrado.