Subtotal column on all worksheets

[kellexlsx]

Hi - I have a workbook with multiple worksheets. All have the same columns, but varying numbers of rows. Is there a simple macro that will give me a bolded total in one particular column - let's say column A, for simplicity - that is one row below the last line of data, and then repeat that process on each successive worksheet?

I've always gotten great advice from the people on this forum, and I hope you all know how much we newbies appreciate your talent and generosity!

 

[Joe4]

There are two ways I wonder consider:

1. Use VBA to automatically add your total rows to the bottom of each sheet

- or -

2. Move the total row to the top, to row 1.
Then in your formula, you can start from row 2 and choose some ending row number that you know that you will never exceed, i.e.

=SUM(A2:A10000)

 

[kellexlsx]

Doing a total row up top is something I hadn't considered, but people in my company go a little bonkers when you change the format of a report on them, and they're used to seeing it at the bottom. So VBA would be preferable, but I'm pretty useless beyond recording simple macros.

Thank you for your suggestion!

 

[Joe4]

OK, which columns do you want to apply it to? Is it only column A, or other columns too?
Is it a simple SUM formula, or a different formula (if different, please post the formula)?
Does the first row of actual data to consider start on row 1, 2, or some other row?

 

[kellexlsx]

In this particular case, I need the sum of column AD, bolded and (I forgot to add) in Accounting format. There is a single, identical header row on each worksheet.

 

[Joe4]

OK, try this:

Sub AddTotals()

    Dim ws As Worksheet
    Dim lr As Long
    
'   Loop through each sheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
'           Find last row with data in column AD
            lr = .Cells(.Rows.Count, "AD").End(xlUp).Row
'           Add total row
            .Cells(lr + 1, "AD").Formula = "=SUM(AD2:AD" & lr & ")"
        End With
    Next ws
    
End Sub

 

[kellexlsx]

Thank you so much for the notes, it makes it so much easier to follow along! This works great - is there an extra line I can add to make the cell bold? I tried

ActiveCell.Font.Bold = True

but it didn't like that.

 

[Joe4]

Thank you so much for the notes, it makes it so much easier to follow along! This works great - is there an extra line I can add to make the cell bold? I tried

ActiveCell.Font.Bold = True

but it didn't like that.

You already have the address of the cell you need here:

.Cells(lr + 1, "AD")

so just add another row like:

Sub AddTotals()

    Dim ws As Worksheet
    Dim lr As Long
    
'   Loop through each sheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
'           Find last row with data in column AD
            lr = .Cells(.Rows.Count, "AD").End(xlUp).Row
'           Add total row
            .Cells(lr + 1, "AD").Formula = "=SUM(AD2:AD" & lr & ")"
'           Make cell bold
            .Cells(lr + 1, "AD").Font.Bold = True
        End With
    Next ws
    
End Sub

 

[kellexlsx]

Perfect. Thank you so much for your help and your patience. You totally made my day.

 

[Joe4]

You are welcome.
Glad I was able to help!