Sum Selection

[al]

Any vba expert can help in making this macro work on at two sheets in a
workbook or 2 workbooks - I would like to place the result in a
diffrent sheet & it shows the proper link.Thxs

Dim UserRange As Range

Output = "=sum(" & Selection.Address(External:=False,
RowAbsolute:=False, ColumnAbsolute:=False) & ")"
Prompt = "Select a cell for the output."
Title = "Select a cell"


On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) 'Range selection

If UserRange Is Nothing Then
MsgBox "Canceled."
Else
UserRange.Range("A1") = Output
End If
End Sub

 

[Tom Ogilvy]

change
External:=False
to
External:=True

 

[al]

Tom
Thxs a lot for your reply !!!
But it does not work properly for non contiguous cells (i.e when I
press control & select cell from different location)
Can you pls improve my macro.Thxs beforehand - I'm nearly there
PLS PLS HELP
THXS

 

[Tom Ogilvy]

Try this: (assume you will be working within the same workbook)

Sub CCC()
Dim UserRange As Range
Dim output as String, Prompt as String
Dim Title as String, sName as String
output = Selection.Address( _
External:=False, RowAbsolute:=False, _
ColumnAbsolute:=False) & ")"
Prompt = "Select a cell for the output."
Title = "Select a cell"
sname = "'" & ActiveSheet.Name & "'!"
output = Replace(output, ",", "," & sname)
output = "=sum(" & sname & output
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) 'Range selection

If UserRange Is Nothing Then
MsgBox "Canceled."
Else
UserRange.Range("A1") = output
End If
End Sub

 

[alan]

THXS A LOT ! IT WORKS ON SAME WORKBOOK (Won't ask you to make it work
on different workbooks as you've already done a lot !)
Pls keep on supporting us !!!