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.
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!
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!
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