VBA-Sum Columns Ignoring Other sheets

[weefatb0b]

I have been asked to have a look at the attached VB to see why it is not working, and after sitting looking at it for quite a few hours, with my limited knowledge, as I am still up skilling VBA, I cannot see why it is not working.

Sub SumColumns()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cell As Range
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" And ws.Name <> "Sheet3" And ws.Name <> "Sheet5" Then
        ' If ws.Name <> "Headcount" And ws.Name <> "Position" And ws.Name <> "Incumbents" And ws.Name <> "Lookup" Then
            lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
            
            For i = 1 To lastRow
                Set cell = ws.Cells(i, 2)
                
                If cell.Value = "Unit Total" Then
                    cell.Offset(0, 1).End(xlToRight).Offset(0, 1).Value = _
                        WorksheetFunction.Sum(ws.Range(cell.Offset(0, 1), cell.Offset(0, 3)))
                End If
            Next i
        End If
    Next ws
End Sub

As you can see I have tried ws.Name both ways and it still does not put the totals in to the required cells.

What it is trying to do, is ignore the 4 sheets named, then for all other sheets in the workbook, look at column B and where the name in the Cell in column B = Unit Total then add up all the values in Column C, then D then E, next to Unit Total and put these values in to the last cell in columns C D & E

Hope I have explained the process well enough?

Thanks

 

[Joe4]

You are welcome.
Glad I was able to help.

If you have any questions regarding my code, please let me know.