how to sum a complete colum on another sheet in VBA

  • Thread starter Thread starter Jean-Pierre D via OfficeKB.com
  • Start date Start date
J

Jean-Pierre D via OfficeKB.com

Hi,
I'am a novice on VBA programming....
On worksheet1 i have cell A1 which must be filled with the sum of colum P on
worksheet2 (without going back and forth between the two sheets)

in excel i would use the formula sum(P:P) on sheet 2 in eg cel A1 en step 2
would be to link cel A1 in sheet1 tot A1 in sheet2....

Does anyone have a clue how to do that in VBA ?
 
Hi Jean-Pierre,

Try:

Sub TestIt()
Dim MySum As Double

MySum = Application.Sum(Sheets("Sheet2").Columns("P:P"))
MsgBox MySum

End Sub
 
Hi Jean Pierre,

My suggestion returns a static value, so go with Topper's dynamic formula
approach.
 
Hi Toppers,

Thanks for the formula but the result is tha in cell "A1" the formula is put
and that is not what i wanted. I would like to have the result of the formula
eg the sum of the column in cell "A1"
Can you help me please?
Thanks,
Jean-Pierre (novice)
Worksheets("Sheet1").Range("a1") = "=SUM(Sheet2!P:P)"
Hi,
I'am a novice on VBA programming....
Click to expand...
[quoted text clipped - 5 lines]
Does anyone have a clue how to do that in VBA ?
Click to expand...
Click to expand...
 
The result in A! is the value as the formula is automatically evaluated.

Otherwise, use Norman's code and add:

Worksheets("Sheet1").Range("a1")=mysum

HTH

Jean-Pierre D via OfficeKB.com said:
Hi Toppers,

Thanks for the formula but the result is tha in cell "A1" the formula is put
and that is not what i wanted. I would like to have the result of the formula
eg the sum of the column in cell "A1"
Can you help me please?
Thanks,
Jean-Pierre (novice)
Worksheets("Sheet1").Range("a1") = "=SUM(Sheet2!P:P)"
Hi,
I'am a novice on VBA programming....
Click to expand...
[quoted text clipped - 5 lines]
Does anyone have a clue how to do that in VBA ?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top