VBA Excel - Copy autosum data from MsgBox or clipboard

Question

I have my values summarized in Excel by this code:

 Sub AutoSum()
 Dim Sumcalc As Integer
 Range("E" & Cells(Rows.Count, "E").End(xlUp).Row + 1).Value = _
 WorksheetFunction.Sum(Range("E2:E" & Cells(Rows.Count, "A").End(xlUp).Row))

 MsgBox (Application.Sum(Range("E2:E" & Cells(Rows.Count, "A").End(xlUp).Row)))
    
 End Sub

and they appear in Msgbox fine. The problem is, that I can't copy this result from there at all.

I tried to change my Msgbox to something like userform with the field

Selectable Text in VBA Message Box

https://www.thespreadsheetguru.com/blog/2015/1/13/how-to-use-vba-code-to-copy-text-to-the-clipboard

and my code finally looks like this:

 Sub AutoSum()
 Dim Sumcalc As Integer
 Range("E" & Cells(Rows.Count, "E").End(xlUp).Row + 1).Value = _
 WorksheetFunction.Sum(Range("E2:E" & Cells(Rows.Count, "A").End(xlUp).Row))

 Sumcalc = Application.Sum(Range("E2:E" & Cells(Rows.Count, "A").End(xlUp).Row))
 Clipboard =.GetData(Sumcalc)
 MsgBox ("Copy to clipboard","Copy Text", Sumcalc)
 
 End Sub

but I am getting an error:

Invalid or unqualified reference for .GetData

In the Msgbox I can't pass a defined variable, just a text value.

How could I copy my Msgbox result to the clipboard or at least making it selectable?

https://stackoverflow.com/a/60896244 ? Is the MsgBox / userform a necessary step, or is your end goal to get the sum in the clipboard? — BigBen, Dec 09 '22 at 14:35
Btw, `Dim Sumcalc As Integer` should likely be `Dim Sumcalc As Double`, to avoid Overflow errors, and silent truncation of decimals. — BigBen, Dec 09 '22 at 14:37
In addition to @BigBen , in that logic, the only thing when I used the function linked, sometimes I had to add a delay to really set up the clipboard, you may use [sleep](https://exceltrick.com/formulas_macros/vba-wait-and-sleep-functions/) but I found wait for 1 second works — Sgdva, Dec 09 '22 at 14:52
PS: Invalid is because you reference `.GetData`, but where is the With that comes from? You're missing `With CreateObject("htmlfile") With .parentWindow.clipboardDat` as shown in the link that you stated and then the `End With` of those — Sgdva, Dec 09 '22 at 14:55

VBasic2008 · Accepted Answer · 2022-12-11T20:05:49.670

Put the Sum in the Clipboard

Sub SumCatcher()
    
    ' Define constants.
    Const PROC_TITLE As String = "Sum Catcher"
    
    ' Reference the worksheet
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
     
    ' Reference the Source range.
    Dim srg As Range
    Set srg = ws.Range("E2:E", ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
    ' Calculate the sum.
    Dim SumCalc As Double, ErrNum As Long
    On Error Resume Next ' prevent error if any cell contains an error
        SumCalc = Application.Sum(srg)
        ErrNum = Err.Number
    On Error GoTo 0
    
    ' Check if the sum was calculated.
    If ErrNum <> 0 Then
        MsgBox "Could not get the sum. Check that no cell contains an error.", _
            vbCritical, PROC_TITLE
        Exit Sub
    End If
    
    ' Reference the first cell below the Source range, the Destination cell.
    Dim dCell As Range: Set dCell = srg.Cells(1).Offset(srg.Rows.Count)
    
    ' Write the sum to the Destination cell.
    dCell.Value = SumCalc
     
    ' Using a late-bound DataObject, write the sum to the clipboard.
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText SumCalc
        .PutInClipboard
    End With
    
    ' Inform.
    MsgBox "The sum is " & SumCalc & ". Use Ctrl+V to paste it.", _
        vbInformation, PROC_TITLE
    
End Sub

VBA Excel - Copy autosum data from MsgBox or clipboard

1 Answers1

Put the Sum in the Clipboard