CODEWITHSUNDEEP
excelvba
Armas.Chuy
Armas.Chuy

Reputation: 17

Sum a value from diferent sheets and return the result on same cell

So, I'm trying to sum a cell from different sheets but I want the sum to be on the same cell or at least the result. The cell that is first summing, is gonna get deleted after the sum.

In this case, the cell A1 is gonna get deleted.

The code only pastes the formula but doesn't do it.

Private Sub C2_Click()
    Sheets("Prueba").Range("A1").Formula = "Sum(Prueba!A1, Reporte!C5)"
End Sub

No error messages though.

Upvotes: 0

Views: 56

Answers (1)

Elbert Villarreal
Elbert Villarreal

Reputation: 1716

Well as Cybernetic.Nomad said you need to use the equal sing = in from of the formula you want. Remember, this is like you where typing in the cell that formula.

Another tip: you can use this 

Private Sub C2_Click()
    Sheets("Prueba").Range("A1").value= Evaluate("=Sum(Prueba!A1, Reporte!C5)
End Sub

This way you tell VBA to get the value returned from the SUM and put it inside the cell A1 as a value, not formula.

Another tip: 

Private Sub C2_Click()
    Sheets("Prueba").Range("A1").Formula= "=SUM(Prueba:Reporte!A1:C5)"
End Sub

This way you can SUM across the sheets and all the sheets in between those sheets any value in the range A1:C5. Where you have "Prueba", "Pueba01", "Pueba02", "Prueba03" and "Reporte", all the values in the range A1:C5, in the sheets between "Prueba" and "Reporte" will be summed. If there is any other sheet outside this to sheets wont be summed in the result.

Also you can use: 

Private Sub C2_Click()
    Sheets("Prueba").Range("A1").value= Evaluate("=SUM(Prueba:Reporte!A1:C5)")
End Sub

Upvotes: 1

Related Questions