VBA - Want to put the sum result at the bottom automatically

Question

I need to put the result of the sum at the end of the last column on different sheets (Not the same number of columns and number of rows)

I need to fix the last part of the code to let that happen.

this is the code (I marked in the code what does not work):

Sub Sum_Dynamic_Rng()

Dim ws As Worksheet
Dim LastCell As Range
    
Dim ColumnNumber As Long
Dim ColumnLetter As String



ColumnNumber = Range("S3").End(xlToLeft).Column

ColumnLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)


    For Each ws In ThisWorkbook.Worksheets

        Set LastCell = ws.Range(ColumnLetter & 2).End(xlDown).Offset(1, 0)
        
   

        **LastCell = WorksheetFunction.Sum(ws.Range(ws.Range(ColumnLetter & 2), ws.Range(ColumnLetter & 2).End(xlDown)))**


    Next ws

End Sub

A side note, but see [a better way to find the last cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Jul 31 '20 at 13:08

score 0 · Answer 1 · answered Jul 31 '20 at 15:06

Your code works one with two bugs

You searched for "LastCell" in the first sheet only because it was before the FOR loop. The spreadsheets have different dimensions from what you wrote, so I moved the search to a loop so that there is a re-check for each iteration.
Another (in my opinion) problem was the reboot which added up the previous calculations. for example. 2 + 2 = 4, 2 + 2 + 4 = 8, 2 + 2 + 4 + 8 = 16 e.t.c.

My code is:

Sub Sum_Dynamic_Rng()

Dim ws As Worksheet
Dim LastCell As String  'I changed from Range to String
Dim ColumnNumber As Long
Dim ColumnLetter As String

    For Each ws In ThisWorkbook.Worksheets
    
        LastColumnNumber = ws.Range("s3").End(xlToLeft).Column
        LastColumnLetter = Split(Cells(1, LastColumnNumber).Address, "$")(1)
        LastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row

        LastCell = LastColumnLetter & LastRow + 1 ' (+1) - last but not the first empty
   
     ws.Range(LastCell) = WorksheetFunction.Sum(ws.Range(ws.Range(LastColumnLetter & 2), ws.Range(LastColumnLetter & LastRow)))
     ws.Range(LastCell).Interior.ColorIndex = 43   ''I added for better visualization
    Next ws

End Sub

If you are satisfied with the answer, give me a plus point from the answer and close the topic, unless you have any questions. :)

score 0 · Answer 2 · answered Jul 31 '20 at 16:18

IT'S OK I did some changes and now it's working!

Sub Sum_Dynamic_Rng()

Dim ws As Worksheet Dim LastCell As String 'I changed from Range to String Dim ColumnNumber As Long Dim ColumnLetter As String

For Each ws In ThisWorkbook.Worksheets

    LastColumnNumber = ws.Range("s3").End(xlToLeft).Column
    LastColumnLetter = Split(Cells(1, LastColumnNumber).Address, "$")(1)
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ***''REPLACE TO A FROM G IN THIS LINE***

    LastCell = LastColumnLetter & LastRow + 1 ' (+1) - last but not the first empty

 ws.Range(LastCell) = WorksheetFunction.Sum(ws.Range(ws.Range(LastColumnLetter & 2), ws.Range(LastColumnLetter & LastRow)))
 ws.Range(LastCell).Interior.ColorIndex = 43   ''I added for better visualization
Next ws

End Sub

If you are satisfied with the answer, give me a plus point from the answer and close the topic, unless you have any questions. :) — Smyhail, Jul 31 '20 at 16:32

VBA - Want to put the sum result at the bottom automatically

2 Answers2