Put sum after row end and apply to all sheets-CodePudding

I have a workbook with multiple worksheets with different names. I wanted to put a sum after the last row of columns A & B and I want it applied to all worksheets in the workbook. Also, I would need that sum to be in bold and highlighted in yellow.

Please help me with the appropriate VBA Macro

EDIT:

I saw this code here too, but this will only put a sum on a single sheet with a predefined sheet name of Sheet1. My sheets are named differently. I would need a macro that would apply what this does to all sheets irrespective of the sheet names.

Sub test() 
    Dim ws as worksheet 
    Dim VR as Long 
    Dim rng as Range

    Set ws = Thisworkbook.Worksheets("Sheet1")  
    With ws
        VR = .Cells(.Rows.Count, 8).End(xlUp).Row  
    End with

    Set rng = ws.Cells((VR 1), 10)  
    rng.Offset(2,0).Formula ="=SUM(J2:J" & VR & ")"
End Sub

CodePudding user response：

You just need to loop through all your worksheets

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets  ' loop through all worksheets
    ' find the last used row in column A in each worksheet
    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' one below last row write the sum formulas
    ws.Cells(LastRow   1, "A").Formula = "=SUM(A2:A" & LastRow & ")"
    ws.Cells(LastRow   1, "B").Formula = "=SUM(B2:B" & LastRow & ")"
Next ws

CodePudding user response：

I would recommend to use the FIND() function as described here

Moreover, you seem to be actively targeting column 10 to put your sum below the last value. The column count could change in the future. To make it more robust, you could either check the column names or always put the sum below the last column of your range. I´d also suggest to use subtotal instead of the SUM function 109, again, just to make it more robust.