Copy sum of range

[bar21967]

Is there a way to copy the sum of a range of cells (A2:A4) and paste the sum in another workbook in cell A2?

Copy from A2:A4
<TABLE style="WIDTH: 59pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=79><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=79>

Amounts​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=17>

161,755.37 ​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=17>

3,503.86 ​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=17>

(71,398.28)​

</TD></TR></TBODY></TABLE>


Paste sum to A2
<TABLE style="WIDTH: 59pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=79><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=17 width=79>

Amounts​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17>

93,860.95​

</TD></TR></TBODY></TABLE>

 

[Derek Brown]

It should work with a macro:

Option Explicit
Public Sub SumRangeToClipboard()
Dim DataObj As New MSForms.DataObject
Dim rng As Range
Dim rng2 As Range
Dim strResult As String
Dim blError As Boolean
'
    blError = False
    Set rng = Selection
    For Each rng2 In rng
        If Not IsNumeric(rng2.Value) Then
            blError = True
        End If
    Next rng2
    If blError Then
      '  MsgBox "Error"
        strResult = "#ERROR"
    Else
        strResult = CStr(Application.WorksheetFunction.Sum(rng))
    End If
    DataObj.SetText strResult
    DataObj.PutInClipboard
    '
    Set DataObj = Nothing
    '
End Sub

This is a simple example where you have to select the range of cells, run the macro and paste the content of the clipboard to the other workbook.
If the ranges/workbooks can be identified, you could of course enhance the macro to do it all on the click of a button or control-key.

 

[bar21967]

I get a compile error when I run the code.

"User-defined type not defined"

 

[Derek Brown]

My apologies - I did not think to check the 'References'.
In (VBA) Tools | References you need to tick the reference to:
Microsoft Forms 2.0 Object Library
Use Alt + F11 to get to the VBA code, click on the module name where the code was placed, set the reference mentioned above and check that it compiles OK using:
Debug | Compile VBAProject

 

[Scott Huish]

Put a SUM formula in the first workbook. Copy this cell. Then Paste Special Values into the other workbook.