How to sum rows in same column and make it dynamic

VbaNoExcel · Jun 3rd 2016

Hi , i am newbie in terms of using VBA and also here. I have a silly query which i cant seem to solve at all.

I have 3 Tabs for 3 products which have Monthly Columns (columns are from column 3 to column 55 as it is for 3 years) and various revenue/ expense rows (rows are from row 6 to row 122). i also have a Total Tab which adds all those product tabs by column and by rows. Not sure how to write a simple code for this. I wrote this crazy lengthy code. Is there a simple code for this?

Thank you Seniors, in advance for your kind help!

Code

Sub Add_All_Three_Products()




Sheets("Total_MB").Range("C6:BC122").ClearContents


For x = 6 To 122


Sheets("Total_Product_Tab").Cells(x, 3) = Sheets("Product_1_Tab").Cells(x, 3) + Sheets("Product_2_Tab").Cells(x, 3) + Sheets("Product_3_Tab").Cells(x, 3)
Sheets("Total_Product_Tab").Cells(x, 4) = Sheets("Product_1_Tab").Cells(x, 4) + Sheets("Product_2_Tab").Cells(x, 4) + Sheets("Product_3_Tab").Cells(x, 4)
Sheets("Total_Product_Tab").Cells(x, 5) = Sheets("Product_1_Tab").Cells(x, 5) + Sheets("Product_2_Tab").Cells(x, 5) + Sheets("Product_3_Tab").Cells(x, 5)
Sheets("Total_Product_Tab").Cells(x, 6) = Sheets("Product_1_Tab").Cells(x, 6) + Sheets("Product_2_Tab").Cells(x, 6) + Sheets("Product_3_Tab").Cells(x, 6)
Sheets("Total_Product_Tab").Cells(x, 7) = Sheets("Product_1_Tab").Cells(x, 7) + Sheets("Product_2_Tab").Cells(x, 7) + Sheets("Product_3_Tab").Cells(x, 7)
Sheets("Total_Product_Tab").Cells(x, 8) = Sheets("Product_1_Tab").Cells(x, 8) + Sheets("Product_2_Tab").Cells(x, 8) + Sheets("Product_3_Tab").Cells(x, 8)
Sheets("Total_Product_Tab").Cells(x, 9) = Sheets("Product_1_Tab").Cells(x, 9) + Sheets("Product_2_Tab").Cells(x, 9) + Sheets("Product_3_Tab").Cells(x, 9)
Sheets("Total_Product_Tab").Cells(x, 10) = Sheets("Product_1_Tab").Cells(x, 10) + Sheets("Product_2_Tab").Cells(x, 10) + Sheets("Product_3_Tab").Cells(x, 10)
Sheets("Total_Product_Tab").Cells(x, 11) = Sheets("Product_1_Tab").Cells(x, 11) + Sheets("Product_2_Tab").Cells(x, 11) + Sheets("Product_3_Tab").Cells(x, 11)
Sheets("Total_Product_Tab").Cells(x, 12) = Sheets("Product_1_Tab").Cells(x, 12) + Sheets("Product_2_Tab").Cells(x, 12) + Sheets("Product_3_Tab").Cells(x, 12)
Sheets("Total_Product_Tab").Cells(x, 13) = Sheets("Product_1_Tab").Cells(x, 13) + Sheets("Product_2_Tab").Cells(x, 13) + Sheets("Product_3_Tab").Cells(x, 13)
Sheets("Total_Product_Tab").Cells(x, 14) = Sheets("Product_1_Tab").Cells(x, 14) + Sheets("Product_2_Tab").Cells(x, 14) + Sheets("Product_3_Tab").Cells(x, 14)
Sheets("Total_Product_Tab").Cells(x, 15) = Sheets("Product_1_Tab").Cells(x, 15) + Sheets("Product_2_Tab").Cells(x, 15) + Sheets("Product_3_Tab").Cells(x, 15)
Sheets("Total_Product_Tab").Cells(x, 16) = Sheets("Product_1_Tab").Cells(x, 16) + Sheets("Product_2_Tab").Cells(x, 16) + Sheets("Product_3_Tab").Cells(x, 16)
Sheets("Total_Product_Tab").Cells(x, 17) = Sheets("Product_1_Tab").Cells(x, 17) + Sheets("Product_2_Tab").Cells(x, 17) + Sheets("Product_3_Tab").Cells(x, 17)
Sheets("Total_Product_Tab").Cells(x, 18) = Sheets("Product_1_Tab").Cells(x, 18) + Sheets("Product_2_Tab").Cells(x, 18) + Sheets("Product_3_Tab").Cells(x, 18)
Sheets("Total_Product_Tab").Cells(x, 19) = Sheets("Product_1_Tab").Cells(x, 19) + Sheets("Product_2_Tab").Cells(x, 19) + Sheets("Product_3_Tab").Cells(x, 19)




Sheets("Total_Product_Tab").Cells(x, 21) = Sheets("Product_1_Tab").Cells(x, 21) + Sheets("Product_2_Tab").Cells(x, 21) + Sheets("Product_3_Tab").Cells(x, 21)
Sheets("Total_Product_Tab").Cells(x, 22) = Sheets("Product_1_Tab").Cells(x, 22) + Sheets("Product_2_Tab").Cells(x, 22) + Sheets("Product_3_Tab").Cells(x, 22)
Sheets("Total_Product_Tab").Cells(x, 23) = Sheets("Product_1_Tab").Cells(x, 23) + Sheets("Product_2_Tab").Cells(x, 23) + Sheets("Product_3_Tab").Cells(x, 23)
Sheets("Total_Product_Tab").Cells(x, 24) = Sheets("Product_1_Tab").Cells(x, 24) + Sheets("Product_2_Tab").Cells(x, 24) + Sheets("Product_3_Tab").Cells(x, 24)
Sheets("Total_Product_Tab").Cells(x, 25) = Sheets("Product_1_Tab").Cells(x, 25) + Sheets("Product_2_Tab").Cells(x, 25) + Sheets("Product_3_Tab").Cells(x, 25)
Sheets("Total_Product_Tab").Cells(x, 26) = Sheets("Product_1_Tab").Cells(x, 26) + Sheets("Product_2_Tab").Cells(x, 26) + Sheets("Product_3_Tab").Cells(x, 26)
Sheets("Total_Product_Tab").Cells(x, 27) = Sheets("Product_1_Tab").Cells(x, 27) + Sheets("Product_2_Tab").Cells(x, 27) + Sheets("Product_3_Tab").Cells(x, 27)
Sheets("Total_Product_Tab").Cells(x, 28) = Sheets("Product_1_Tab").Cells(x, 28) + Sheets("Product_2_Tab").Cells(x, 28) + Sheets("Product_3_Tab").Cells(x, 28)
Sheets("Total_Product_Tab").Cells(x, 29) = Sheets("Product_1_Tab").Cells(x, 29) + Sheets("Product_2_Tab").Cells(x, 29) + Sheets("Product_3_Tab").Cells(x, 29)
Sheets("Total_Product_Tab").Cells(x, 30) = Sheets("Product_1_Tab").Cells(x, 30) + Sheets("Product_2_Tab").Cells(x, 30) + Sheets("Product_3_Tab").Cells(x, 30)
Sheets("Total_Product_Tab").Cells(x, 31) = Sheets("Product_1_Tab").Cells(x, 31) + Sheets("Product_2_Tab").Cells(x, 31) + Sheets("Product_3_Tab").Cells(x, 31)
Sheets("Total_Product_Tab").Cells(x, 32) = Sheets("Product_1_Tab").Cells(x, 32) + Sheets("Product_2_Tab").Cells(x, 32) + Sheets("Product_3_Tab").Cells(x, 32)
Sheets("Total_Product_Tab").Cells(x, 33) = Sheets("Product_1_Tab").Cells(x, 33) + Sheets("Product_2_Tab").Cells(x, 33) + Sheets("Product_3_Tab").Cells(x, 33)
Sheets("Total_Product_Tab").Cells(x, 34) = Sheets("Product_1_Tab").Cells(x, 34) + Sheets("Product_2_Tab").Cells(x, 34) + Sheets("Product_3_Tab").Cells(x, 34)
Sheets("Total_Product_Tab").Cells(x, 35) = Sheets("Product_1_Tab").Cells(x, 35) + Sheets("Product_2_Tab").Cells(x, 35) + Sheets("Product_3_Tab").Cells(x, 35)
Sheets("Total_Product_Tab").Cells(x, 36) = Sheets("Product_1_Tab").Cells(x, 36) + Sheets("Product_2_Tab").Cells(x, 36) + Sheets("Product_3_Tab").Cells(x, 36)
Sheets("Total_Product_Tab").Cells(x, 37) = Sheets("Product_1_Tab").Cells(x, 37) + Sheets("Product_2_Tab").Cells(x, 37) + Sheets("Product_3_Tab").Cells(x, 37)






Sheets("Total_Product_Tab").Cells(x, 39) = Sheets("Product_1_Tab").Cells(x, 39) + Sheets("Product_2_Tab").Cells(x, 39) + Sheets("Product_3_Tab").Cells(x, 39)
Sheets("Total_Product_Tab").Cells(x, 40) = Sheets("Product_1_Tab").Cells(x, 40) + Sheets("Product_2_Tab").Cells(x, 40) + Sheets("Product_3_Tab").Cells(x, 40)
Sheets("Total_Product_Tab").Cells(x, 41) = Sheets("Product_1_Tab").Cells(x, 41) + Sheets("Product_2_Tab").Cells(x, 41) + Sheets("Product_3_Tab").Cells(x, 41)
Sheets("Total_Product_Tab").Cells(x, 42) = Sheets("Product_1_Tab").Cells(x, 42) + Sheets("Product_2_Tab").Cells(x, 42) + Sheets("Product_3_Tab").Cells(x, 42)
Sheets("Total_Product_Tab").Cells(x, 43) = Sheets("Product_1_Tab").Cells(x, 43) + Sheets("Product_2_Tab").Cells(x, 43) + Sheets("Product_3_Tab").Cells(x, 43)
Sheets("Total_Product_Tab").Cells(x, 44) = Sheets("Product_1_Tab").Cells(x, 44) + Sheets("Product_2_Tab").Cells(x, 44) + Sheets("Product_3_Tab").Cells(x, 44)
Sheets("Total_Product_Tab").Cells(x, 45) = Sheets("Product_1_Tab").Cells(x, 45) + Sheets("Product_2_Tab").Cells(x, 45) + Sheets("Product_3_Tab").Cells(x, 45)
Sheets("Total_Product_Tab").Cells(x, 46) = Sheets("Product_1_Tab").Cells(x, 46) + Sheets("Product_2_Tab").Cells(x, 46) + Sheets("Product_3_Tab").Cells(x, 46)
Sheets("Total_Product_Tab").Cells(x, 47) = Sheets("Product_1_Tab").Cells(x, 47) + Sheets("Product_2_Tab").Cells(x, 47) + Sheets("Product_3_Tab").Cells(x, 47)
Sheets("Total_Product_Tab").Cells(x, 48) = Sheets("Product_1_Tab").Cells(x, 48) + Sheets("Product_2_Tab").Cells(x, 48) + Sheets("Product_3_Tab").Cells(x, 48)
Sheets("Total_Product_Tab").Cells(x, 49) = Sheets("Product_1_Tab").Cells(x, 49) + Sheets("Product_2_Tab").Cells(x, 49) + Sheets("Product_3_Tab").Cells(x, 49)
Sheets("Total_Product_Tab").Cells(x, 50) = Sheets("Product_1_Tab").Cells(x, 50) + Sheets("Product_2_Tab").Cells(x, 50) + Sheets("Product_3_Tab").Cells(x, 50)
Sheets("Total_Product_Tab").Cells(x, 51) = Sheets("Product_1_Tab").Cells(x, 51) + Sheets("Product_2_Tab").Cells(x, 51) + Sheets("Product_3_Tab").Cells(x, 51)
Sheets("Total_Product_Tab").Cells(x, 52) = Sheets("Product_1_Tab").Cells(x, 52) + Sheets("Product_2_Tab").Cells(x, 52) + Sheets("Product_3_Tab").Cells(x, 52)
Sheets("Total_Product_Tab").Cells(x, 53) = Sheets("Product_1_Tab").Cells(x, 53) + Sheets("Product_2_Tab").Cells(x, 53) + Sheets("Product_3_Tab").Cells(x, 53)
Sheets("Total_Product_Tab").Cells(x, 54) = Sheets("Product_1_Tab").Cells(x, 54) + Sheets("Product_2_Tab").Cells(x, 54) + Sheets("Product_3_Tab").Cells(x, 54)
Sheets("Total_Product_Tab").Cells(x, 55) = Sheets("Product_1_Tab").Cells(x, 55) + Sheets("Product_2_Tab").Cells(x, 55) + Sheets("Product_3_Tab").Cells(x, 55)




Next x




End Sub

Display More

**cytop** · Jun 3rd 2016

Re: How to sum rows in same column and make it dynamic

Perhaps...

Code

Sub Refresh()
   
   Dim c As Excel.Range
   Dim r As Excel.Range
   
   Dim RowCounter As Long
   Dim ColCounter As Long
   
   Range("C6:BC122").ClearContents
   
   For RowCounter = 6 To 122
      
      For Each r In Range("D1").CurrentRegion
      
         For ColCounter = 3 To 55
            Cells(RowCounter, ColCounter).Value = Cells(RowCounter, ColCounter).Value + Sheets(r.Value).Cells(RowCounter, ColCounter).Value
         Next
      Next
   
   Next
   
End Sub

Display More

Add the sheet tab names going across from D1, E1 & F1. - you can extend it simply by adding another name to the next column...

Rough and ready, written freehand an untested. As written it should be added to the code module for the master sheet, easy enough to move to a module, just change the lines:

Code

For Each r In Sheets("Total_Product_Tab").Range("D1").CurrentRegion
      
            Sheets("Total_Product_Tab").Cells(RowCounter, ColCounter).Value = Sheets("Total_Product_Tab")Cells(RowCounter, ColCounter).Value + Sheets(r.Value).Cells(RowCounter, ColCounter).Value

to refer to the master sheet rather than using default addresses.

VbaNoExcel · Jun 4th 2016

Re: How to sum rows in same column and make it dynamic

hi cytop

many thanks. let me understand and digest your reply. please accept this message as a BIG Thank You!

VbaNoExcel · Jun 4th 2016

Re: How to sum rows in same column and make it dynamic

Hi Cytop

I ran the code but got error message "subscript out of range". not sure what i am doing wrong. so, i created a simple sample file and then ran code but still same error message. i have attached the file here. really confused.

**cytop** · Jun 4th 2016

Re: How to sum rows in same column and make it dynamic

Quote

Add the sheet tab names going across from D1, E1 & F1

You didn't add the sheet names. I was working blind so just picked somewhere I thought would be out of the way. Added to D1, E1 & F1 on sheet5 for this example instead. You can add these anywhere (even in the hidden rows) - just refer to the first cell in the line:

Code

For Each r In Sheets("Sheet5").Range("D1").CurrentRegion

If you use CurrentRegion, the range must be separated by 1 blank row and column all around, otherwise refer to the exact range

Code

For Each r In Sheets("Sheet5").Range("D1:F1")

Code

Sub Refresh()
     
    Dim c As Excel.Range
    Dim r As Excel.Range
     
    Dim RowCounter As Long
    Dim ColCounter As Long
     
    Sheets("Total_Product_Tab").Range("C6:BC122").ClearContents
     
    For RowCounter = 6 To 122
         
        For Each r In Sheets("Sheet5").Range("D1").CurrentRegion
             
            For ColCounter = 3 To 55
                Sheets("Total_Product_Tab").Cells(RowCounter, ColCounter).Value = Sheets("Total_Product_Tab").Cells(RowCounter, ColCounter).Value + Sheets(r.Value).Cells(RowCounter, ColCounter).Value
            Next
        Next
         
    Next
     
End Sub

Display More

Just to make absolutely sure, a working copy attached. Some values on the individual product sheets have been changed to check the total propagates correctly. There's a button on the total sheet to run the procedure.

As the sheets are identical, you could also Copy/PasteSpecial/Add the entire range from each sheet, but let's get the simplistic approach working first even though it is by far the slowest method. However, 3 or so seconds to consolidate multiple large sheets is not that bad considering...

VbaNoExcel · Jun 4th 2016

Re: How to sum rows in same column and make it dynamic

hi cytop

many thanks. let me understand it again. i am trying to understand line by line. BIG Thank You again!

VbaNoExcel · Jun 30th 2016

Re: How to sum rows in same column and make it dynamic

Hi Cytop

Many thanks. I finally understood your codes. took me long time. Many thanks for your kind assistance.

How to sum rows in same column and make it dynamic

Participate now!

Share