建议增加一个拉动区域进行求和，并把求和数放进粘贴板的功能

saimenl

Sub Clipboard11()
On Error Resume Next
rngsum = 0
Dim a As New DataObject '声明新DataObject 类
Dim c As Range, rng As Range

If Selection.Count = 1 Then
Set rng = Selection
Else
Set rng = Selection.SpecialCells(xlCellTypeVisible)
End If

For Each c In rng
rngsum = rngsum + c
Next
'MsgBox rngsum
'a.SetText rngsum  '设定文本
'a.PutInClipboard  '送入剪贴板
rngsum = Round(rngsum, 2)
ClipBoard_SetData (rngsum)
End Sub




=================================


Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Declare PtrSafe Function CloseClipboard Lib "User32" () As LongPtr
Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long
Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As Long) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

Function ClipBoard_SetData(MyString As String)
   Dim hGlobalMemory As Long, lpGlobalMemory As Long
   Dim hClipMemory As Long, x As Long

   ' Allocate moveable global memory.
   '-------------------------------------------
   hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

   ' Lock the block to get a far pointer
   ' to this memory.
   lpGlobalMemory = GlobalLock(hGlobalMemory)

   ' Copy the string to this global memory.
   lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

   ' Unlock the memory.
   If GlobalUnlock(hGlobalMemory) <> 0 Then
      MsgBox "Could not unlock memory location. Copy aborted."
      GoTo OutOfHere2
   End If

   ' Open the Clipboard to copy data to.
   If OpenClipboard(0&) = 0 Then
      MsgBox "Could not open the Clipboard. Copy aborted."
      Exit Function
   End If

   ' Clear the Clipboard.
   x = EmptyClipboard()

   ' Copy the data to the Clipboard.
   hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:

   If CloseClipboard() = 0 Then
      MsgBox "Could not close Clipboard."
   End If

End Function

saimenl

然后再设置一个快捷键，我目前自己用的加载宏设置的是Ctrl+a

saimenl

应用场景：填各种报表时，需填合计数，不用记数字

Kevin

非常感谢分享案例和代码，请问合计数为啥不直接用SUM公式？

saimenl

Kevin 发表于 2024-4-24 08:07
非常感谢分享案例和代码，请问合计数为啥不直接用SUM公式？

因为使用场景中，有可能求和不连续的单元格。例如下图的黄色单元格，常规操作是先按下Ctrl键选择，然后记下状态栏的求和数：70819.95。再填入另外一个报表中，这样容易记错数值。也不够快捷

Kevin

了解了，谢谢。