エクセル2000VBA コマンドボタンがうまく動きません

質問

こんにちは、VBA初心者です。

２枚のシートがあり、「入力」というシートの任意のセルを、「出力」という別シートにコピーするという作業をコマンドボタンによって行いたいと思っています。

自分で作ってみたものは、このようになります。

Private Sub 任意の行のコピー・印刷_Click()

Application.ScreenUpdating = False

'セル内容コピー
Selection.Copy
Sheets("出力").Select
Range("A9").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("入力").Select
ActiveCell.Offset(0, 1).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("出力").Select
Range("C9").Select
（以下略）

ところが、これだと
「実行時エラー1004　RangeクラスのSelectメソッドが失敗しました」と表示され、デバッグをかけると6行目のRange("A9").Selectが黄色く反転した状態になります。

どうしてでしょうか。
同じ内容のものをマクロで登録し、実行すると、何の問題もなく動くのですが…
ほとほと困っております。
どうかよろしくお願いします。

回答No.4

ja7awu · ベストアンサー

コードを記述しているシート以外は、シート名を明示してセルを指定するようにします。

あと、単一のセルをアクティブ セルにするには、Activate メソッドを使用します。
まぁ、Select でも影響は、ありませんが・・・

こんな感じで如何でしょうか。

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
'セル内容コピー
Selection.Copy
Sheets("出力").Activate
ActiveSheet.Range("A9").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("入力").Activate
ActiveCell.Offset(0, 1).Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("出力").Activate
ActiveSheet.Range("C9").Activate
'（以下略）
End Sub

お礼

無事、うまくいきました…！
長々とおつきあいいただき、感謝に堪えません。
本当にありがとうございました！

回答No.6

ctrlzr

自分が回答したのに、「参考になる以前の回答」を探すのが大変でした(^^ゞ
要は、Wendy02さんのようなコードを書けばOKということです。
ついでに補足です。
.Select や .Activate を書かないのは、マクロ実行時に、カーソルやシートを動かさないためです。"入力"シートから"出力"シートへコピーするのに、.Activateを繰り返すと、シートがパラパラと入れ替わってしまいます。見てると疲れるので書かない方がいいです。

お礼

ご丁寧にありがとうございます。
「以前の回答」を探すの、難しいですよね。私も常々感じています。

皆様のおかげで、無事完成にこぎ着けることができました。
皆様と、このサイトのありがたみをかみしめています。

回答No.5

Wendy02

＞要は微に入り細に入り、セルの場所を指定しておけばこのようなエラーは起きなかった、という解釈でよろしいでしょうか。

そういうことなんですね。

標準モジュールで書くと、Rangeオブジェクトの属性が、Application(Excel)になっているので、シートの配下（メンバ）になっていないのです。

記録マクロでエラーが出ないのは、標準モジュールに書かれたからなのです。

でも、記録マクロから脱皮するためには、やっぱり、「微に入り細に入り」をしてあげるというのは、大事なことなんですね。ですが、あまり、ごちゃごちゃしてしまっても、自分で分からなくなってしまうので、インデントを付けて、コードを構造的に書くということや、「With」で省略してやれば、見やすくなって、エラーも減るわけです。

「Select をいれない」ということを言う方がいますが、Selectをすると、その分、オブジェクトを取得しているために、時間が掛かるというわけですが、その時間というのは、数万個で、やっと１秒程度の違いが出てくるという話なのです。

「Selectを入れない」のは、Selectを入れる必要性がないからなのですね。

例えば、以下のようなコードをみて考えてみてください。
入力側は、A9 から１つずつ横に値を、出力側の、A9 に２つずつ横に値を出力する、という内容です。今回のご質問には、参考にはならないかもしれませんが、このように、Selectを使う場面が出てきません。

Private Sub CommandButton1_Click()
　Dim i As Long
　Dim sh1 As Worksheet, sh2 As Worksheet
'
　Set sh1 = Sheets("入力")
　Set sh2 = Sheets("出力")
　For i = 0 To 9
　　'出力側　　= 入力側
　　sh2.Range("A9").Offset(, i * 2).Value _
　　　　　　　　= sh1.Range("A9").Offset(, i).Value
　Next i
　Set sh1 = Nothing
　Set sh2 = Nothing
End Sub

お礼

またまた、補足をありがとうございます。
昨日は本当に、おのれの未熟さを感じた一日でした…

回答No.3

Wendy02

まず、少し、言葉で、おやりになりたいことを説明していただいたほうが良いようですね。
確かに、記録マクロでも有効な場合があるのですが、不安定な要素が多いかと思います。

記録マクロで行うと不明な点
　Selection　(どこの範囲？）
　 Range("A9")（どこのA9？)
　 ActiveCell(どこのアクティブセル？）

ということになってしまいます。ただし、Selection は、自分が選んだ範囲なら、それは、それで構わないのですが、それを使いまわししていくのでしたら、変数で確保したほうが安全です。

さらに、どこにマクロを書こうとも、それ自体は、正しい記述で書けば、クラスモジュール以外ではそれほど問題はないはずですが、ExcelのVBAは基本的には、標準モジュールに書くことになっています。例外的に、コマンドボタンを含めたイベント（何かをすると起動するマクロ）で、シートに属しているものや、グローバルなイベント、それ自体には意味を持たないインスタンスやプロパティの生成のためのコードです。

エラーになった部分に対しては、

例えば、
　Range("A9")
という時に、シートモジュールなら、そのシートの属性になりますが、そうでないと、エラーになりますね。

その理由は、
Sheets("出力")
のRange("A9")というつもりでしょうけれども、
「入力」というシートのモジュールに書けば、暗黙的に、Range("A9") は、「入力」シートの属性になっています。

標準モジュールで書く書かないというよりも、Rangeオブジェクトは大きく分けると、二つあると考えてよいのです。

１つは、Application(Excel) の配下(メンバ）と、もう１つはシートの配下（メンバ）があります。シートのメンバを呼ばれても、違うシート（違う国のようなもの）になれば、そのメンバを呼んでも、そこには居ません。だからエラーになるわけですね。

だから、正しく、Sheet("入力").Range("A9") というように、苗字と名前のように呼んであげれば、ほぼ、どこでも間違いないわけです。

お礼

こんばんは。

＞その理由は、
Sheets("出力")
のRange("A9")というつもりでしょうけれども、
「入力」というシートのモジュールに書けば、暗黙的に、Range("A9") は、「入力」シートの属性になっています。

もしかして、この部分がポイントだったのでしょうか？

要は微に入り細に入り、セルの場所を指定しておけばこのようなエラーは起きなかった、という解釈でよろしいでしょうか。

（１日２日締め切らずにおきますので、もし激しい勘違いをしていましたらご指摘お願いできますでしょうか）

回答No.2

ctrlzr

マクロで動いて、ボタンで動かない理由は、コードの記述場所のちがいかと思われます。
マクロは、標準モジュール、ボタンは、Sheet1（入力）のところに書いてありますよね？
上記のコードでは、記述場所で違う動きになってしまいます。
selectとselectionは使用しなくてもコードは書けるので、そのようなコードにすれば、違いは出ないと思います。
コードは、私の以前の回答を参考にしてください。

お礼

こんばんは、ご回答ありがとうございます。

本当に申し訳ないのですが、「参考になる以前の回答」を具体的に教えて頂けませんでしょうか。
（ここ一ヵ月ぶんくらいのものは検索してみたのですが）

ソース（？）をいじるようなVBAは今日始めたばかりなので、「selectとselectionを使用しないコード」というものが、全く見当がつきません…

どうかよろしくお願いします。

回答No.1

bin-chan

６行目を以下のように変更するとどうなりますか？

Sheets("出力").Range("A9").Select

補足

書き込みありがとうございます。
やってみましたが、残念ながら情況は変わらず、6行目が黄色になっています。
ちなみに、今は↓のようになっています。

'セル内容コピー
Selection.Copy
Sheets("出力").Range("A9").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("入力").Select