Incorporating sheet loop

Question

Hi there i have this code which only runs on a single sheet(sheet3) but i want it to loop through other sheets of the workbook and run this code. I tried using the for each loop but it does not seem to be compatible with this code. Ive looked up other methods of looping but im really unsure of how do i go about it .

Here is the code

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

Answer 1

Usually you can loop through sheets using their index #, or the mentioned for each... So added to your code this would mean:

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

Usually it is also better to name the "next", because you have a better overview which for...next loop is closed.