Copy, paste as sum

O

Ouchini

Hello,

Is there an easy way to copy multiple values and paste the sum of these
values in one cell. So, I select the values of A.1, A.2 and A.3. I copy
the cells and wants to paste the sum in another worksheet.
 
D

davesexcel

=SUM(Sheet1!$A$1:$A$3)

I think this is what you mean!:)
Place this formula in other sheets, you will always get the sum of
sheet 1 A1:A3
 
O

Ouchini

Hello davesexcel,

I am affraid this is not what I mean. When I drag my mouse over several
cells with values and copy those cells, I want to paste the sum of them
in another cell. I sometimes have to select several cells which are not
in a range and want to avoid using a helpcell.

In the statusline I can see what the sum is so I want to paste the sum
in 1 cell and not the individual cells.

Ouchini
 
M

Miyahn

In the statusline I can see what the sum is so I want to paste the sum
in 1 cell and not the individual cells.
Click to expand...

If you want to copy 'AutoCalculate' result according to your selection on
'AutoCalclate' menu, apply proper shortcut key to the following macro.

Option Explicit
Sub CopyAutoCalcResult()
If TypeName(Selection) <> "Range" Then Exit Sub
Dim ACF As String, Buf As String
ACF = AutoCalcFunc
On Error Resume Next
With Application.WorksheetFunction
Select Case ACF
Case 2013: Buf = .Average(Selection)
Case 2014: Buf = .CountA(Selection)
Case 2015: Buf = .Count(Selection)
Case 2016: Buf = .Max(Selection)
Case 2017: Buf = .Min(Selection)
Case 2018: Buf = .Sum(Selection)
Case Else: Buf = ""
End Select
End With
If Err Then Buf = ""
On Error GoTo 0
With CreateObject("htmlfile")
.ParentWindow.ClipboardData.SetData "Text", CStr(Buf)
End With
End Sub
'
Private Function AutoCalcFunc() As Long
Dim aBtn As CommandBarButton
With Application.CommandBars("AutoCalculate")
For Each aBtn In .Controls
If aBtn.State Then AutoCalcFunc = aBtn.ID: Exit For
Next aBtn
End With
Set aBtn = Nothing
End Function
 
O

Ouchini

Thank you for this great solution. Although I am not good in VBA, I
understand part of the code. Maybe Microsoft includes this function as
a right click dropdown menu item in a future version of Excel.
 
Top