how to sum a complete colum on another sheet in VBA

[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) 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 ?

 

[Norman Jones]

Hi Jean-Pierre,

Try:

Sub TestIt()
Dim MySum As Double

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

End Sub

 

[Guest]

Worksheets("Sheet1").Range("a1") = "=SUM(Sheet2!P)"

 

[Norman Jones]

Hi Jean Pierre,

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

 

[Jean-Pierre D via OfficeKB.com]

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)"

Hi,
I'am a novice on VBA programming....

[quoted text clipped - 5 lines]

Does anyone have a clue how to do that in VBA ?

 

[Guest]

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

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)"

Hi,
I'am a novice on VBA programming....

[quoted text clipped - 5 lines]

Does anyone have a clue how to do that in VBA ?