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]

It is very hard to help without being able to see what the data in your sheets looks like.
Can you post a sample of what the data in one of these sheets looks like, and also show us what your expected results look like?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[weefatb0b]

Sorry, hope this helps

Position Number	Position Title	Establishment Count	Occupied	Vacant	Vacant %	Unit/Dept	Serv. Area	HOS
540100	Position 1	1.00	1.00	0.00		Unit 1	Service area 1	Head of Service 1
540220	Position 2	1.00	1.00	0.00		Unit 1	Service area 1	Head of Service 1
540221	Position 3	1.00	1.00	0.00		Unit 1	Service area 1	Head of Service 1
540222	Position 4	1.00	1.00	0.00		Unit 1	Service area 1	Head of Service 1
540440	Position 5	2.00	2.00	0.00		Unit 1	Service area 1	Head of Service 1
540441	Position 6	3.00	3.00	0.00		Unit 1	Service area 1	Head of Service 1
540442	Position 7	1.51	1.51	0.00		Unit 1	Service area 1	Head of Service 1
540620	Position 8	1.00	1.00	0.00		Unit 1	Service area 1	Head of Service 1
540621	Position 9	1.51	1.51	0.00		Unit 1	Service area 1	Head of Service 1
540622	Position 10	1.00	1.00	0.00		Unit 1	Service area 1	Head of Service 1
540623	Position 11	0.51	0.51	0.00		Unit 1	Service area 1	Head of Service 1
540800	Position 12	0.59	0.59	0.00		Unit 1	Service area 1	Head of Service 1
540920	Position 13	0.41	0.41	0.00		Unit 1	Service area 1	Head of Service 1
	Unit Totals.	15.53	15.53	0.00	0.0%	Unit 1	Service area 1	Head of Service 1
585180	Position 1	1.45	0.00	1.45		Unit 2	Service area 1	Head of Service 1
585181	Position 2	0.00	1.00	-1.00		Unit 2	Service area 1	Head of Service 1
585185	Position 3	0.00	1.00	-1.00		Unit 2	Service area 1	Head of Service 1
	Unit Totals.	1.45	2.00	-0.55	-37.9%	Unit 2	Service area 1	Head of Service 1
541100	Position 1	1.00	1.00	0.00		Unit 3	Service area 1	Head of Service 1
541220	Position 2	3.00	1.00	2.00		Unit 3	Service area 1	Head of Service 1
541221	Position 3	0.00	1.00	-1.00		Unit 3	Service area 1	Head of Service 1
541440	Position 4	3.83	2.45	1.38		Unit 3	Service area 1	Head of Service 1
541800	Position 5	0.54	0.54	0.00		Unit 3	Service area 1	Head of Service 1
	Unit Totals.	8.37	5.99	2.38	28.4%	Unit 3	Service area 1	Head of Service 1
585100	Position 1	0.95	1.00	-0.05		Unit 4	Service area 1	Head of Service 1
585420	Position 2	1.03	1.03	0.00		Unit 4	Service area 1	Head of Service 1
	Unit Totals.	1.98	2.03	-0.05	-2.5%	Unit 4	Service area 1	Head of Service 1
542100	Position 1	1.00	1.00	0.00		Unit 5	Service area 1	Head of Service 1
542200	Position 2	1.00	0.51	0.49		Unit 5	Service area 1	Head of Service 1
542201	Position 3	0.00	1.00	-1.00		Unit 5	Service area 1	Head of Service 1
542220	Position 4	1.04	0.38	0.66		Unit 5	Service area 1	Head of Service 1
542221	Position 5	0.00	0.51	-0.51		Unit 5	Service area 1	Head of Service 1
542420	Position 6	2.20	1.94	0.26		Unit 5	Service area 1	Head of Service 1
542800	Position 7	0.51	0.51	0.00		Unit 5	Service area 1	Head of Service 1
	Unit Totals.	5.75	5.85	-0.10	-1.7%	Unit 5	Service area 1	Head of Service 1
		Sum of each of cells in column above where Column B = Unit Totals.	Sum of each of cells in column above where Column B = Unit Totals.	Sum of each of cells in column above where Column B = Unit Totals.

 

[Joe4]

One thing I see right off the bat. Your code is looking for "Unit Total":

If cell.Value = "Unit Total" Then

but your values are actually:
"Unit Totals."

So it would not find any matches at all.

 

[weefatb0b]

One thing I see right off the bat. Your code is looking for "Unit Total":

If cell.Value = "Unit Total" Then

but your values are actually:
"Unit Totals."

So it would not find any matches at all.

Thanks, never noticed that, when I change it to the correct cell name, its kind of working, but its putting a value in Column J instead of the row at at the bottom, .

 

[Joe4]

Do you understand what this line is doing?

cell.Offset(0, 1).End(xlToRight).Offset(0, 1).Value = ...

It is moving over one column (from B to C), then going all the way over to the right to the last column with data (column I), then moving over one more column (column J).
And that is where it is entering your formula.
It is not doing anything with columns C, D, or E.

 

[weefatb0b]

Do you understand what this line is doing?

cell.Offset(0, 1).End(xlToRight).Offset(0, 1).Value = ...

It is moving over one column (from B to C), then going all the way over to the right to the last column with data (column I), then moving over one more column (column J).
And that is where it is entering your formula.
It is not doing anything with columns C, D, or E.

No Joe, I wasnt sure tbh, as I said I am very much at the upskilling stage and trying to learn, so would I be better advising a rewrite so they get it correct?

 

[Joe4]

Yeah, the code, as written, will not do what you want.

If you are wanting a grand total at the bottom, to sum just the lines where column B is equal to "Unit Totals.", you do not need to loop through every cell in column B.
You can just put a SUMIF formula in the last row of each sheet.
If you need help updating your code to do that, please let us know and we can probably help with that.

 

[Joe4]

If I understand your question correctly, I think this should do what you want:

Sub SumColumns()
    Dim ws As Worksheet
    Dim lastRow As Long
   
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" And ws.Name <> "Sheet3" And ws.Name <> "Sheet5" Then
'           Get last row in column B
            lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
'           Insert formulas into columns C, D, and E
            ws.Cells(lastRow + 1, "C").Formula = "=SUMIF($B2:$B" & lastRow & ",""Unit Totals."",C2:C" & lastRow & ")"
            ws.Cells(lastRow + 1, "D").Formula = "=SUMIF($B2:$B" & lastRow & ",""Unit Totals."",D2:D" & lastRow & ")"
            ws.Cells(lastRow + 1, "E").Formula = "=SUMIF($B2:$B" & lastRow & ",""Unit Totals."",E2:E" & lastRow & ")"
        End If
    Next ws
End Sub

 

[weefatb0b]

You understood correctly and this does exactly what is required. Thanks for all your help.