[[20160317145146]]　『マクロのコードについて』（あい）　ページの最後に飛ぶ

『マクロのコードについて』（あい）

マクロボタンを用いて下記のようなコードを組みたいです。

・J6～Q13をコピーしてH6～O13に貼り付ける
・V6～W13をコピーしてP6～Q13に貼り付け
・J19～Q47をコピーしてH19～Q47に貼り付け
・V19～W47をコピーしてP19～Q47へ貼り付け

貼り付けは値のみ貼り付けで、１回押したら２回目は押せないようにしたいです。

よろしくお願いいたします。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 マクロの記録をまず試して、わからないところを聞かれたらいかがでしょう？
（稲葉） 2016/03/17(木) 15:13

 >マクロボタン
 コマンドボタン？シート上に配置しているものですか？

 >・J6～Q13をコピーしてH6～O13に貼り付ける 
 範囲にまちがいないですか？（J6～O13は上書きしてしまうけど・・）
 N6～O13とP6～Q13は同じものとなりますが、これも問題なし？

（ろっくん） 2016/03/17(木) 15:26

マクロの記録してみました。

' Macro8 Macro
'

    Range("J6:Q13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("H6:I6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("V6:W13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P6:Q6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J19:Q47").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("H19:I19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("V19:W47").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P19:Q19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H3").Select
End Sub
マクロボタンとはフォームボタンのことでした。
上記コードでフォームボタンを押すと、「既にデータがありますが置き換えますか」とでるので、出ないようにしたいのと、２回押さないようにしたいです。

（あい） 2016/03/17(木) 16:07

 フォームボタン？
 ユーザーフォーム上のコマンドボタンのことですか？

 フォームモジュール（なければシートモジュール）に
 Private Sub CommandButton1_Click()
    Range("H6:O13").Value = Range("J6:Q13").Value
    Range("P6:Q13").Value = Range("V6:W13").Value
    Range("H19:Q47").Value = Range("J19:Q47").Value
    Range("P19:Q47").Value = Range("V19:W47").Value
    Me.CommandButton1.Enabled = False
 End Sub

 Rangeで始まっている行は各セルの値を別セルに代入しているものです。
 値のコピーと同様の結果となります。

 オブジェクト.Enabled = False
 とするとそのオブジェクトを無効化します。（要は押せなくなる）
 押せるようにするには
 Me.CommandButton1.Enabled = True
 のようにします。

 2回押さないように・・・とありますが、一度押したら二度と押すことはないんですか？
（ろっくん） 2016/03/17(木) 17:30

 横入り失礼します。

 なんとなく、2回処理しない　という意味は、　４種類の貼り付け操作で、それぞれ1回ずつという気もしないでもないです。

 ここは、質問者さんに確認してもらわなければいけないのですが。

（β） 2016/03/17(木) 17:44

 To あい　さん

 仮に各領域あたり、貼り付け1回のみ　という仕様だったとして
 1回のみというのは、どういうサイクルでしょうか？

 たとえば、今日1回処理する。
 で、10年後に、このブックを開く。10年前に処理されているから、もう貼り付けＮＧですか？

 それとも、ブックを開いたあと、閉じるまでの間に1回だけということですか？

（β） 2016/03/17(木) 17:46

 ↑　それとも、貼付け先領域が　すべて空白セルでない場合に　ＮＧ　というか　貼り付けスキップ　ということでしょうか？

 それと

 >>既にデータがありますが置き換えますか

 ほんとに、アップされたコードで、このメッセージがでるのですか？
 コピペですから、メッセージなしで、無条件にコピペされると思いますが？

（β） 2016/03/17(木) 18:01

 >ほんとに、アップされたコードで、このメッセージがでるのですか？
 >コピペですから、メッセージなしで、無条件にコピペされると思いますが？
 これが参考になるだろうか？

 A1セルとB1セルを結合、C1セルとD1セルを結合、同じようにA2セルとB2セル、C2セルとD2セルを結合。

 2行目の結合セルに値を入力。
 1行目の結合セル二つを選択してコピー、2行目の結合セルに貼り付け。

 これで「コピーまたは移動先のセルの内容を置き換えますか？」（2010の場合）というメッセージが表示される。

 結合セルを複数選択して同じ大きさの結合セルに張り付ける場合、貼り付け先に値がある場合に表示されるようだ。
（ねむねむ） 2016/03/18(金) 09:52

 追記で。
 2010の場合、「コピーまたは移動先のセルの内容を置き換えますか？」のメッセージは

 Application.DisplayAlerts = False 
 で、表示されなくなった。
（ねむねむ） 2016/03/18(金) 09:59

まとめてみると、こんなかんじ？

 Private Sub CommandButton1_Click()
    Dim i As Long

    Application.DisplayAlerts = False

    For i = 0 To 3
        Range(Array("J6:Q13", "V6:W13", "J19:Q47", "V19:W47")(i)).Copy
        Range(Array("H6:O13", "P6:Q13", "H19:Q47", "P19:Q47")(i)).PasteSpecial Paste:=xlPasteValues
    Next i

    Application.DisplayAlerts = True
    Application.CutCopyMode = False
    Range("A1").Select
 End Sub
（???） 2016/03/18(金) 10:23

おっと、簡単な上書きチェックを入れ忘れました。

 Private Sub CommandButton1_Click()
    Dim i As Long

    If Range("H6") = "" Then
        Application.DisplayAlerts = False

        For i = 0 To 3
            Range(Array("J6:Q13", "V6:W13", "J19:Q47", "V19:W47")(i)).Copy
            Range(Array("H6:O13", "P6:Q13", "H19:Q47", "P19:Q47")(i)).PasteSpecial Paste:=xlPasteValues
        Next i

        Application.DisplayAlerts = True
        Application.CutCopyMode = False
        Range("A1").Select
    End If
 End Sub
（???） 2016/03/18(金) 10:27

ＨＩ，ＪＫ，ＬＭ，ＮＯ，ＰＱ，ＶＷがセルの結合されています。
言葉足らずですいません。
2回目押させないようにというのは、VＷの数値が更新されていたら、マクロ実行で更新されていない（数値が変わっていなかったらマクロは実行されない）といった形にしたいです。

現在、HIには2012年のデータ、JKには2013年、LMには2014年、NOには2015年、PQには2016年のデータが入力されており、（5年間）。VWには2017年（最新）のデータが入っています。
フォームコントロールボタンを押せば、2017年を含めた5年間のデータHI～PQに表示されるようにしたいのです。
（あい） 2016/03/18(金) 15:15

 思い付きですが。

 ・シートのすべてのセルを選択して、書式で、保護タブのロックのチェックをいったんはずし
 ・シート保護。すべての項目にチェック（ユーザーに許可）をいれる。

 ・コードでは
 Dim er As Long

 On Error Resume Next
 コピペ　処理
 er = Err.Number
 On Error Goto 0

 If er <> 0 Then
    MsgBox "すでに転記済みなので、上書きできません"
 Else
    コピペした領域のセルの書式で、保護をロック
 End If

 といった構えも考えられるかもしれません。
 ただし、結合セルによるエラーについては考慮していません。
 結合セルであってもエラーができない転記処理にしておくことが必要です。

（β） 2016/03/18(金) 16:17

こんにちは。

あれ、わたしのところでは結合セルなしでもそのメッセージが出ます。
コピー元とコピー先が一部重なっているせいかな、と思っていましたが
みなさんのところでは出ないのですか。

それはともかく、あいさんに質問です。
コピペをしていいときと、コピペをしてはいけないときは
シートを目で見て判断できますか？

つまり、もし目で見て判断できるなら、それをマクロに教え込めば
ダメなときはコピペをしないコードが作れると思うのです。

（　佳　） 2016/03/18(金) 19:09

 >>みなさんのところでは出ないのですか。

 いえ、でますね。
 アップされたコードをみないまま、コピー元とコピー先が重なっているというケース自体が
 頭の中になかったというか想定外でしたので。

 で、重なっているのは、そういう必要性があるからなんでしょうね？

 いずれにしても、要件がよくわかりません。
 2回コピペしないとか、重なっている領域をコピペしてメッセージがでないようにしたいとか、
 そういう、現象面の説明はあるのですが、そもそも、何をしたいのかというところが見えません。

 要件によっては、現象面というか機能面での対応ではなく、処理方法として適したものがあるかもしれないかも　と思ったりしています。

（β） 2016/03/18(金) 19:30

 こんにちは。

 βさん。それなんですよ。
 何がしたいのか分からない、何が問題なのか分からない、
 １回しか実行しないならマクロは必要ないじゃない？
 そんなメッセージがホントに出るの？　と疑問だらけで。
 ちょうどマクロの記録のコードがアップされていたので、ためしに動かしてみたのです。

 わたしがやってみたのは、J6:Q13に"aa"と入れ、V6:W13に"bb"と入れて、
 あいさんのアップされたコードそのままを、連続して何度か実行です。
 すると１回実行するごとにaaの領域が狭まって行き、５回実行すると全部bbになりました。
 それで なんとなくやりたいことの輪郭は分かるような気がしました。
 なるほどこりゃ拙いだろうとも思いました。

 そのうえで  あいさんの2016/03/18(金) 15:15のコメントを見ると
 過去５年分保存データの年度更新かなぁ、と思えます。

（　佳　） 2016/03/19(土) 08:23

 To 佳さん

 説明ありがとうございます。
 説明に従って、もう一度Ｑ／Ａを読み返し、実際にシートに結合セル含めた環境を準備して
 アップされたMacro8 を実行して、あぁ、こういうことがしたかったんだと理解できました。

 To あい　さん

 で、あらためて、あぁ、これは　なかなか難しいなと感じました。
 コード処理のことではありません。コードは（ワーニングが出ないようにすることも含めて）なんとでもなりますから。

 今、2012～2016 がセットされているとして、VW に 2017 を記入して、実行。
 結果、2012 がなくなり、2013～2017 になって、めでたしめでたし。

 で、うっかりもう一度実行すると、2014、2015、2016、2017、2017 になってしまう！
 これを避けたい　ということですよね。

 たとえば、１つの方法として思いつくのが、実行前に、PQ と VW の値を比較して、もし、同じなら取り込み済みなので処理をしない。

 でも、

 ・うっかりか、必要があっては別にして VWの値の一部を変更。実行時とは違っているので平気で実行されてしまう。
 ・全項目数値が、たまたま前年度と同じだった。で、取り込み済みと認識されてしまって、取り込みたくても取り込めない。

 また、こういう場合、ありませんか？

 ・2017が確定した（と、思った）
 ・取り込み処理をして、2013～2017 になった。
 ・そのあと、2017の数値に間違いを発見。（あるいは実行時の2017が未確定のままの数値だった）
 ・なので、【2017だけを】再度、取り込みたい。

 あるいは、未確定の状態で取り込んでしまったので、【元に戻したい】

 まずは、こういった運用面でのシナリオを十分に検討して、どういう機能にするか、そこを
 しっかりと考えられたほうがいいかもしれません。

 コード自体は、そのシナリオと、決められた運用仕様で、いかようにもできますので。

（β） 2016/03/19(土) 09:14

 βの考え方は↑の通りで、あまりコード面で先走らないほうがいいと思っていますが
 とりあえず、ワーニングがでる件、値転記なのに　Copy->PasteSpecial で処理しているからです。

 ２つあるブロックの上のほうだけですが、サンプルです。

 Sub ワーニングが出ない取り込み()

    Range("H6:O13").Value = Range("J6:Q13").Value

    Range("P6:Q13").Value = Range("V6:W13").Value

 End Sub

（β） 2016/03/19(土) 09:26

皆様わかりづらい説明で申し訳ありません

β様

今、2012～2016 がセットされているとして、VW に 2017 を記入して、実行。

ＶＷには他のシートから抽出した2017年のデータが参照されています。
最新のデータが参照されるようになっています。

結果、2012 がなくなり、2013～2017 になって、めでたしめでたし。

 で、うっかりもう一度実行すると、2014、2015、2016、2017、2017 になってしまう！
 これを避けたい　ということですよね。

そうです。今年度も含めた5年間を表示させたいのです。
というもの弊社ＰＣが苦手なヒトが多く、コピーペーストさえもままならず、ボタンひとつで変更されれば楽になると思ったからです。

たとえば、１つの方法として思いつくのが、実行前に、PQ と VW の値を比較して、もし、同じなら取り込み済みなので処理をしない。

 でも、
 ・うっかりか、必要があっては別にして VWの値の一部を変更。実行時とは違っているので平気で実行されてしまう。
 ・全項目数値が、たまたま前年度と同じだった。で、取り込み済みと認識されてしまって、取り込みたくても取り込めない。
 また、こういう場合、ありませんか？

P6：Q6には見出しとして年度の表示「Ｈ26年度」とあり、Ｖ6：Ｗ6には「Ｈ27年度」とあります。Ｐ6：Ｑ6とＶ6：Ｗ6が異なる場合、他のデータを更新なら大丈夫でしょうか。
全項目数値が前年度と同じことはありえるかもしれませんが、項目が売り上げや消費電力等なので可能性は低いかなと思います。

 よろしくお願いいたします。
（あい） 2016/03/22(火) 15:47

コメント返信：

[ 一覧(最新更新順) ]

[[20160317145146]] 『マクロのコードについて』（あい） ページの最後に飛ぶ

[[20160317145146]]　『マクロのコードについて』（あい）　ページの最後に飛ぶ