Summing all rows in a column in a specified worksheet

[NukedWhale]

I need to sum all the rows, not including the header row, in a column specified by the user for a specific worksheet.

The number of rows is MyRows
The user specified column is WeightingRange. WeightingRange is a string and the user entered in the column letter as text. I.e WeightingRange=G.
The worksheet is SourceSheet

I found a website that recommended using excel's built in functions.

This is an example they gave:
[VBA]MySum = Application.WorksheetFunction.Sum(Range("A1:A100"))[/VBA]

How would I adapt this for what I want to do? This is how far I am. But I don't know how to make it refer to a specific worksheet, or if it would even work.
[VBA] MySum = Application.WorksheetFunction.Sum(Range("&Weightingrange & "2:" & WeightingRange & str(MyRows)&"))[/VBA]

[Bob Phillips]

[vba]

MySum = Application.WorksheetFunction.Sum(Range(WeightRange & 2).Resize(MyRows))
[/vba]

[NukedWhale]

That looks much simpler and digestible then the code I spewed. Would the line of code I added make sure that the calculation is performed on my sourcesheet?

[VBA]Sheets(SourceSheet).Activate
MySum = Application.WorksheetFunction.Sum(Range(WeightRange & 2).Resize(MyRows)) [/VBA]

[NukedWhale]

Dang, this line of code gets a type mismatch error

[VBA] Sheets(SourceSheet).Activate[/VBA]

[Bob Phillips]

Maybe?

[vba]

SourceSheet.Activate [/vba]

[NukedWhale]

That worked.

This line gave me an error
[VBA]MySum = Application.WorksheetFunction.Sum(Range(WeightRange & 2).Resize(MyRows)) [/VBA]

Run-time error '1004'
Method 'Range' of object '_Global' failed

Any ideas?

[Bob Phillips]

What's in WeightedRange and in MyRows?

[NukedWhale]

WeightingRange is a column in my source sheet (SourceSheet) that has numeric values 0-10 that I use to weight my results.

The sum of the values in my WeightingRange is what I'll use as a base size to calculate some percentages.

Does this help at all? Let me know if you need any further clarification. You've helped me out a ton over the past week. I really really appreciate everything you've helped me with so far.

Here's a few snapshots of code.

[VBA]
Dim WeightingRange As String
Dim MyRows As Long

'Prompt for Weighting Range
WeightingRange = InputBox("Please enter the column letter of the column used for weighting")
'Count the number of rows in the worksheet
MyRows = SourceSheet.UsedRange.Rows.Count
[/VBA]