Easier Method

[Steven]

I have the following macro that is assigned to a button on the formatting bar.

Note: The macro is in a different file than the file where the cells are
selected.

Sub SumSelectedCells()
Application.ScreenUpdating = False
Dim wb As Workbook
Set wb = ActiveWorkbook
wb.Activate
myVar = InputBox("Enter Cell Location", "Create Variable")
Range(myVar).Value =
Application.WorksheetFunction.Sum(Application.Selection)
Windows("zCalc.xls").Close (0)
Application.ScreenUpdating = True
End Sub

So what this does is if I have cells highlighted in a file and I want to get
the sum in a cell I click on the button to run this macro. The only thing is
I have to input a cell location for the macro to put the value. How can I do
this without having to input a cell address. The ideal would be : I
highlight the cells ; click to run the macro ; then click in a cell and paste
.... or something like that .

Thank you ,

Steven

 

[Norman Jones]

Hi Steven,

Try something like:

'==========>>
Public SumSelectedCells()
Dim mySum As Double
Dim Rng As Range

mySum = Application.Sum(Selection)

On Error Resume Next
Set Rng = Application.InputBox( _
Prompt:="Selectation cell", _
Title:="Sum Cell", _
Type:=8)
On Error GoTo 0

If Not Rng Is Nothing Then
Rng.Cells(1).Value = mySum
End If
End Sub
'<<==========

 

[Norman Jones]

Hi Steven,

There is a sdignificant typo in my
suggested code.

Public SumSelectedCells()

Should read:

Public Sub SumSelectedCells()