Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
7
Help us understand the problem. What is going on with this article?
@seigo

Excelで選択したセルの合計値をクリップボードにコピーする

More than 3 years have passed since last update.

選択したセルの合計値をクリップボードにコピーするExcelマクロです。
これでステータスバーに表示されている合計値を覚えておかなくても済みます。
個人用マクロブック(PERSONAL.XLSB)に保存し、"Ctrl+Shift+C"のようなショートカットキーを割り当てておくと便利です。
WorksheetFunction.Sum(Selection)Sumを書き換えれば、平均、個数、最小値、最大値などもコピーできます。

Excel.png

Sub 合計値をコピー()
'
' マクロ名: 合計値をコピー
' ショートカット キー: Ctrl+Shift+C
'

    If Not TypeOf Excel.Selection Is Excel.Range Then
        Call Excel.Selection.ShapeRange.PickUp 'PickUp = 本来のCtrl+Shift+Cの動作
        Exit Sub
    End If

    Dim mySelection As String
    mySelection = Application.WorksheetFunction.Sum(Selection)
    With CreateObject("Forms.TextBox.1")
        .MultiLine = True
        .Text = mySelection
        .SelStart = 0
        .SelLength = .TextLength
        .Copy
    End With

    ' 確認のポップアップが必要なら、コメントを外す
    ' Call VBA.MsgBox("選択されているセルの合計値をコピーしました" & vbLf & mySelection, , "選択値の合計")

End Sub

最近のWindows(64ビット版)ではDataObjectが不安定なため、代わりにTextBoxを利用。

7
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
seigo
Product Manager / UX Designer at Cinnamon, Inc.

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
7
Help us understand the problem. What is going on with this article?