> 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.
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

Signature
Miyahn (Masataka Miyashita) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2006 - Dec 2006)
HQF03250@nifty.ne.jp
Ouchini - 09 Mar 2006 14:32 GMT
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.