Adding rows to multiple sheets

[Latoya]

I have a report that contains 177 sheets. Is there a way to add a row that will total colums A-AA at the end of each sheet. Please keep in mind that the last row of each sheet varies.

 

[kpark91]

I think running a macro would be the best choice.
In your ThisWorkbook Module, insert this code

Sub kpark91()
    Dim WS As Worksheet, LR As Long, col As Long
    
    For Each WS In Worksheets
        LR = WS.Range("A" & Rows.count).End(xlUp).Row
        If LR <> 1 Then
        For col = 1 To 27
            WS.Cells(LR, col).Value = WorksheetFunction.Sum(Range(Cells(1, col), Cells(LR - 1, col)))
        Next col
        End If
    Next WS
End Sub

I haven't tested the code so run the macro on a copy of your workbook.

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming the correct row to put the sums can be determined from column A, try the code below.

I suggest you test this on a workbook with a lot less sheets than 177 to start with.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Total_Each_Sheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br>        ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1) _<br>            .Resize(, 27).FormulaR1C1 = "=SUM(R1C:R[-1]C)"<br>    <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[anandvarma]

group all sheet and in one sheet you insert rows, that will be populated to all sheet.

how to group sheets

select the sheet1 and hold shift key and press sheet 177 now all are grouped.

insert rows or columns you want to insert in one sheet.

to ungroup
on the sheet name tap right click with mouse and ungroup it

hope this will work

 

[Peter_SSs]

I haven't tested the code ...

You probably should as there are a couple of problems with it.

group all sheet and in one sheet you insert rows, that will be populated to all sheet.

I don't think you have taken account of this:

Please keep in mind that the last row of each sheet varies.

 

[kpark91]

You probably should as there are a couple of problems with it.

Show me the wayyyyyy :D
I don't have a working VBE

 

[Peter_SSs]

I don't have a working VBE

That certainly would make it tricky to test.

Show me the wayyyyyy :D

Are you asking for an explanation of the issues I see with your code?

 

[kpark91]

That certainly would make it tricky to test.
Are you asking for an explanation of the issues I see with your code?

Yes I am

 

[Peter_SSs]

I see three problems with the code.

1. Over-write of data
LR = WS.Range("A" & Rows.count).End(xlUp).Row
This finds the last row of data, meaning that the following line over-writes this last line of data
WS.Cells(LR, col).Value = ...

2. SUM not adding all rows of data.
Because LR finds the last row of data, the following only adds up to the second last row of data.
WorksheetFunction.Sum(Range(Cells(1, col), Cells(LR - 1, col)))

3. Not referencing the correct sheet.
Because your WorksheetFunction line does not include the red bits I have added below, the cells being summed are always cells from whatever the active sheet is when the code is called.
WorksheetFunction.Sum(Range(WS.Cells(1, col), WS.Cells(LR - 1, col)))

 

[kpark91]

Ohhhh, I see.
Thank you good sir!!!