セルをクリックで選択。違うセルへ価をコピーしたいです。

質問

お知恵をお貸しください。
エクセルでA列のセルを１つ選択して、その価を違うセル（例えばE12）に自動でコピーさせたいのですが良い方法がわかりません。マクロも試してみたのですが、A列のみの選択がうまくいかず動きませんでした。（A列は番号の入力域で、空欄が下へ続きます。）
単純なことの質問で申し訳ありませんが、よろしくお願いいたします。

回答No.2

ham_kamo · ベストアンサー

WorksheetのイベントにBeforeClickがあれば簡単なのですが、BeforeRightClickやBeforeDoubleClickはあるのに、BeforeClickがないんですよねえ。

私も同じようなことをしようとして、No.1さんのようなやり方で以前やりましたが、矢印キーでA列にセルを移動してもこのイベントが走ってしまい、そのときはそれではまずかったので、苦労した経験があります。

結局、そのブックだけ右クリックメニューに追加して、右クリック→メニュー選択とするようにしました。

たとえば以下のような感じです。これではSheet1のA列で右クリックしたときに、E17にコピーするようにしています。

標準モジュールに以下を記述

Public NewMenu As CommandBarButton

Sub CopyToE12()
　Range("E12").Value = Selection.Cells(1, 1).Value
End Sub

そしてThisWorkbookに以下を記述

Private Sub Workbook_Open()
　Set NewMenu = Application.CommandBars("Cell").Controls.Add(before:=1)
　With NewMenu
　　.Caption = "E12へコピー"
　　.OnAction = "CopyToE12"
　End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
　NewMenu.Delete
End Sub

そしてシートオブジェクト(Sheet1など)に以下を記述。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　If Selection.Columns(1).Column = 1 Then
　　NewMenu.Enabled = True
　Else
　　NewMenu.Enabled = False
　End If
End Sub

Private Sub Worksheet_Deactivate()
　NewMenu.Enabled = False
End Sub

補足

ご回答ありがとうございます。
右クリックメニューとは考えませんでした。
コードを試したのですが、実行時エラー91、オブジェクト変数またはWithブロック変数が設定されていません。と出てデバックすると
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　If Selection.Columns(1).Column = 1 Then
　　NewMenu.Enabled = True←の行で止まります。

環境で違いが出るのでしょうか？

回答No.6

ham_kamo

No.2です。

> 新しいブックを開き右クリックをすると「E12へコピー」のメニューが現れ、
> クリックするとマクロを記入していたブックが開かれ、
> マクロが開けませんのエラーが出ます。

他のブックを開いたときにはそのメニューは現れないはずですが、どうやらエラーが出たときにゴミが残ってしまったようですね。

対処法ですが、まずExcelをいったん終了し、もう一度起動して新規のブックを作成します。そしてAlt+F11でVBAの画面を立ち上げ、以下のマクロをコピーして貼り付けてください。（貼り付けるのはどこでもかまいません）

Sub DelMenu()
　Dim m
　For Each m In Application.CommandBars("Cell").Controls
　　If m.Caption = "E12へコピー" Then m.Delete
　Next
End Sub

これをF5キーで実行すると、これで「E12へコピー」という「右クリックのメニューが削除されます。シート上で右クリックしてこのメニューがなくなったことを確認したら、そのブックは閉じてかまいません。

お礼

ありがとうございます。
メニュー出なくなりました。
色々な方法があるんですね。

回答No.5

imogasi

#3です。お礼部分に関連して、効果の出る列を限定してみました。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static m
If Target.Column = 1 And Target.Row < 10 Then
m = Target
End If
If Target.Column <> 1 And Target.Column = 3 Then 'Ｃ列
ActiveCell = m
End If
End Sub
で、Ｃ列とコメントしたところにTarget.Column = 3を入れて
３＝Ｃ列以外では反応しないようにしました。
Ｆ列ならTarget.Column = 6と修正すること。
全部やめるには
Ｓｔａｔｉｃ　ｍの前に
exit Sub
を入れてください。
セルと関連づけるなら
If range("A1")="" then Exit Sub
を入れる。Ａ１セルが空白なら全処理をパスします。
全行コメント化や全行抹消してしまう方法もあります。

お礼

わざわざ考えて頂きありがとうございました。
列の指定で他では反応しなくなりました。

回答No.4

ham_kamo

No.2です。

>　NewMenu.Enabled = True←の行で止まります。

とありますが、マクロの最初の
Public NewMenu As CommandBarButton

Sub CopyToE12()
　Range("E12").Value = Selection.Cells(1, 1).Value
End Sub

は標準モジュール（Module1など）に貼り付けていますでしょうか。
Workbook_Open()ではエラーが出てないようなので、間違えてThisWorkBookに貼り付けていませんか？

どうしてもエラーが出るようだったら、シートのマクロはなくても一応動きます。この
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
と
Private Sub Worksheet_Deactivate()
は、右クリックに追加したメニューがSheet1のA列のときだけ表示し、それ以外のセルで右クリックしたときには色が薄くなって選択できないようにしているだけです。これがなくてもやりたいことはできるのですが、右クリックメニューで「E12に追加」がどのセルでもできる状態になります。A列以外で使わなければいいだけの話ですが。

補足

回答ありがとうございます。
ご指摘の通り貼り付ける場所を間違えておりました。
Private Sub Workbook_Open()
　Set NewMenu = Application.CommandBars("Cell").Controls.Add(before:=1)
　With NewMenu
　　.Caption = "E12へコピー"
　　.OnAction = "CopyToE12"
　End With
End Sub
の記述を標準モジュールに書いておりました。
ThisWorkbookに書き込みすると動くようになりました。
ありがとうございました。

新たにご相談があります。
違うマクロを試す為に教えていただいた記述を削除いたしました。
新しいブックを開き右クリックをすると「E12へコピー」のメニューが現れ、クリックするとマクロを記入していたブックが開かれ、マクロが開けませんのエラーが出ます。
削除の仕方がまずかったのでしょうか？
メニューを消す方法があれば教えてください。お願いいたします

回答No.3

imogasi

単純なことではないと思います。
綱渡り的に
Ｓｈｅｅｔ１のselection_Changeイベントに貼り付ける。
Ａ列例データ
a
b
c
d
e
f
g
h
i
j
ーー
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static m
If Target.Column = 1 And Target.Row =< 10 Then
m = Target
End If
If Target.Column <> 1 Then
ActiveCell = m
End If
End Sub
--
まずセットしたいデータの入ったＡ列のセルをクリック。
Ａ列以外をクリック。
選んた値がセットできます。
ーー
上記コードでボロが出ないことを願う。

お礼

ご回答ありがとうございました。
指定以外のセルにも簡単に貼り付けることが出来ました。
ただ次にクリックした所にも張り付いてしまうので、気をつけてつかいます。解除の方法もあるのでしょうか？

回答No.1

zap35

シート名右クリック　→　コードの表示　で開く画面に以下のマクロを貼り付けます。A列をクリックするとそのセルの値をE12セルに書きこむマクロです

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　If Target.Column = 1 Then　'A列は列番号が１、もしB列なら２
　　Range("E12").Value = Target.Value
　End If
End Sub

お礼

ご回答ありがとうございます。
簡単な記述で出来るのですね！
列の指定もわかりました。
クリック以外にもセルがアクティブになれば書き込まれるので色々と使えますね。
ありがとうございました。