VBA Pasteで教えて下さい！

質問

Book1で指定したセル範囲(C2:C41)を
Book２のSheet1の（AB4:AB43）とSheet2の（AU10:AU49）にペーストを行うに
あたり、ボタン押下にて実行する下記コードをBook1に作成しましたが、
２番目のPasteで実行時エラー1004「アプリケーション定義または
オブジェクト定義のエラーです」と出ます。
複数Sheetの別々のセルにペーストする方法を
教えていただけないでしょうか？
Book1とBook２は共に開いた状態です。
Book２のシートは、それぞれVBAで保護をかけていますが、
.protect UserInterfaceOnly:=Trueで変更可能な状態に
しています。

Private Sub　copy_Click()

　Range("C2:C41").Copy
　Windows("Book2").Activate

　Sheets("Sheet1").Range("AB4:AB43").PasteSpecial Paste:=xlPasteValues
　Sheets("Sheet2").Range("AU10:AU49").PasteSpecial Paste:=xlPasteValues
　（↑ここでエラーが出ます）

End sub

回答No.4

mitarashi · ベストアンサー

#3です。
ブックを開くときに実行すべきと思います。
マクロ無効で開いた時に、他のブックのマクロから操作されるのも防止するための仕様かもしれませんね。

Private Sub Workbook_Open()
With ThisWorkbook
.Sheets("Sheet1").Protect UserInterfaceOnly:=True
.Sheets("Sheet2").Protect UserInterfaceOnly:=True
End With
End Sub

ブックの保護を外していじった後の保護かけ忘れを防ぐためには、 Workbook_BeforeCloseにも入れておいても良いかもしれません。

お礼

mitarashi様、度々の回答有難うございます。
また、お礼が遅くなり申し訳ありません。

やはりUserInterfaceOnly:=Trueがキーポイントでした。
まだまだ知識不足で、UserInterfaceOnly:=Trueの
使い方を知らないまま、コードに組み込んでいたのが
コピペできない原因だったようです。恐らく・・・。

Openする時とBeforeCloseする時にUserInterfaceOnly:=Trueを
入れてエラーが出なくなりました。

何度もご丁寧に回答頂き大変感謝いたします。
ありがとうございました。

回答No.5

seastar3

最初の曖昧な助言でどうも失礼しました。
結局、検証してみると、次のコードでできました。

Private Sub CommandButton1_Click()
Windows("Book2.xlsx").Activate
Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("C2:C41").Copy Destination:=Worksheets("Sheet1").Range("AB4")
Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("C2:C41").Copy Destination:=Worksheets("Sheet2").Range("AU10")
Windows("Book1.xlsm").Activate
End Sub

　antsuさんのコードが思い通り働かなかったのは、たぶん、コピーバッファの連続ペーストができていないのが原因なのでしょう。これは一般的操作でもよくある事だと思います。
　従って、このコードは２回ともコピー元を指定しています。そしてコピー先は先頭番地のみを指定しています。
　もし、コピーしたものを連続ペーストするための便利な設定があるのなら、それが正解なのでしょう。
　また、コピー値を独自のセルに一時保存して貼り付けていく方法も考えられます。

お礼

seastar3様、度々スミマセン。

教えていただいたコードで試してみたのですが、
何故かエラーになってしまいました。

結果として、恐らくUserInterfaceOnly:=Trueの使い方を
私が間違っていた為のものだったと思います。
再検証する余裕がなく、再検証できておりません。

余裕ができたら、勉強の為、テストしてみたいと思います。

何度もご回答頂き本当にありがとうございました。

回答No.3

mitarashi

#2です。

>Book２のシートは、それぞれVBAで保護をかけていますが、.protect UserInterfaceOnly:=Trueで変更可能な状態にしています。

これを見落としていましたが、下記を実行後でも大丈夫でした。
Sub test()
With Workbooks("Book2.xlsx")
.Sheets("Sheet1").Protect UserInterfaceOnly:=True
.Sheets("Sheet2").Protect UserInterfaceOnly:=True
End With
End Sub

念のため、下記は大丈夫でしょうか？

なお注意しなければいけないのは、引数UserInterfaceOnlyにTrueを指定して保護したワークシートをそのまま保存して閉じた場合です。次にそのブックを開いたときには、ユーザーの手動操作だけでなくマクロによる操作も保護されています。
http://officetanaka.net/excel/vba/sheet/sheet07.htm

補足

回答有難うございます。
拡張子は.Xlsmを記載しておりました。
（質問のコードに記載漏れでした）

最初のWithでは、エラーが出ましたが、
上記のSub test()を実行後は、
エラーが出ませんでした。

最後に書かれております注意点で
再度お尋ねしたいのですが、

UserInterfaceOnly:=True実行のまま
ワークシートを保存→閉じた場合ということで
解釈していいのでしょうか？
また、この場合、閉じる時にUserInterfaceOnly:=Trueを
指定すればいいということなのでしょうか？
URLも見てみましたが、理解力が低くて今ひとつ
わかりませんでした。
度々すみませんが、教えて頂けると嬉しいです。

回答No.2

mitarashi

下記は、拡張子が必要かと思いますが、大丈夫ですか？
Windows("Book2").Activate
("Book2.xls"とか、"Book2.xlsx"とか）
新規作成時（保存前）なら動きますが、一回保存したが最後動かなくなると思いますので、避ける方が無難です。

下記は動作しました。
また、コメントアウトしたコードの様に、ペースト先をActivateしなくてもペースト可能です。ご参考まで。
Private Sub CommandButton1_Click()
Range("C2:C41").Copy
Windows("Book2.xlsx").Activate

Sheets("Sheet1").Range("AB4:AB43").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet2").Range("AU10:AU49").PasteSpecial Paste:=xlPasteValues

' Range("C2:C41").Copy
' With Workbooks("Book2.xlsx")
' .Sheets("Sheet1").Range("AB4:AB43").PasteSpecial Paste:=xlPasteValues
' .Sheets("Sheet2").Range("AU10:AU49").PasteSpecial Paste:=xlPasteValues
' End With

End Sub

回答No.1

seastar3

確かめていませんが、お急ぎと思いますのでヒントを書けば、Book2のSheet1を操作した直後なので、Sheet2は操れないのでしょう。下のコードの2行目を挿入して、Sheet2をクリックした状態、すなわちアクティブにしてから貼り付ける必要があります。

　Sheets("Sheet1").Range("AB4:AB43").PasteSpecial Paste:=xlPasteValues
　Sheets("Sheet2").Activate
　Sheets("Sheet2").Range("AU10:AU49").PasteSpecial Paste:=xlPasteValues

補足

回答有難うございます。
早速教えていただいた内容で試しましたが
ダメでした。
Sheet２をアクティブにした状態で止めてみると、
すでにクリップボードから消えた状態でSheet上で
右クリック→貼付が選択できない状況でした。
何故消えてしまうのでしょうか？