実行時エラー1004 Pasteの失敗

質問

シート(式マスタ)に、それぞれの追加したいシートに合わせた式を入力した行を複数用意しておき,
行を追加したいシート(資産Ａ)またはシート（資産Ｂ）に、以下のようなＶＢＡを登録したボタンを置いておきます。
このシート(資産Ａ)またはシート（資産Ｂ）のボタンを押すごとに、シート(式マスタ)に入力してあるそれぞれのシートにあった行を、１行ずつ追加されるようにしたつもりでした。
このボタンは１回目は無事動くのですが、もう一行増やそうとボタンを押すと、

　　　　実行時エラー1004　
　　　　ＷｏｒｌｓｈｅｅｔクラスのPasteメソッドが失敗しました

と表示されます。

初めからできないのならまだわかるのですが。。
どのようにしたらいいかご教示していただけたら幸いです。

Sub 行追加()
Application.ScreenUpdating = False

Dim SHlast As Long '各シートの最終行を取得

With Sheets("式マスタ")
Select Case ActiveSheet.Name

Case "資産Ａ"
.Range("B5:FO5").Copy
Case "資産Ｂ"
.Range("B7:FO7").Copy

End Select
End With

With ActiveSheet
.Unprotect

SHlast = .Cells(.Rows.Count, 2).End(xlUp).Row
.Paste Destination:=.Range(.Cells(SHlast + 1, 2), .Cells(SHlast + 1, 171))

.Protect UserInterfaceOnly:=True
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

回答No.1

noname#144013 · ベストアンサー

こんにちは。

詳しいことは判りませんが、どうやら、『シートの保護』が設定
されているシートに対して、Unprotectメソッド を実行すると、
それまでコピーされていたものが破棄（クリップボードの内容が
クリア）されてしまうようです。
注）Excelのバージョンによっては異なるかもしれません。

そのため、Pasteメソッド の実行時にエラーとなってしまうよう
です。
１回目で成功していたのはマクロ実行前に、まだ『シートの保護』
が設定されていなかったためだと思われます。

この状態のままだと、
　マクロ実行時のエラーでマクロを中断終了させた場合は、
　既にUnprotectメソッドが実行された後なので、次のマクロ
　実行時には、コピー＆ペーストは成功するが、その段階では
　今度は、Protectメソッドが実行されているので、次のマクロ
　実行時には、再びエラーとなってしまう。
　（要するに１回毎に成功と失敗を繰り返す）
という状態になるものと思われます。

これを回避するには、Copyメソッドを実行する前に Unprotect
メソッドを実行してしまえば良いと思います。

以下のマクロは、ご提示のマクロに上記の回避処置を追加
させて頂いたものです。
宜しければ、検証してみて下さい。
※当方は、Excel2000で検証しました。

■改造版マクロ
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
'== 数式セルの行追加処理 ==
Sub 行追加2()

　　Dim sSh1Name As String　'式のコピー元のシート名
　　Dim sSh2Name As String　'式のコピー先のシート名
　　Dim bCopy As Boolean　　'コピー実行の可/不可フラグ
　　Dim SHlast As Long　　　'各シートの最終行位置の取得用

　　'表示更新の抑止設定
　　Application.ScreenUpdating = False

　　'シート名の取得
　　sSh1Name = "式マスタ"　　　　'式のコピー元のシート名
　　sSh2Name = ActiveSheet.Name　'式のコピー先のシート名

　　'コピー先のシートを操作対象とする
　　With Worksheets(sSh2Name)
　　　　'ここでシート保護を解除
　　　　.Unprotect

　　　　'現在のデータ最終行を取得
　　　　SHlast = .Cells(.Rows.Count, 2).End(xlUp).Row

　　　　'式コピー元のシートより数式セルをコピー
　　　　'※コピー先のシート別にコピー範囲を切替え
　　　　bCopy = True　'コピー実行の可／不可フラグを[可]で初期化
　　　　With Worksheets(sSh1Name)
　　　　　　Select Case sSh2Name
　　　　　　Case "資産Ａ"
　　　　　　　　.Range("B5:FO5").Copy
　　　　　　Case "資産Ｂ"
　　　　　　　　.Range("B7:FO7").Copy
　　　　　　Case Else
　　　　　　　　'上記以外のシートだったらコピー実行を[不可]とする
　　　　　　　　bCopy = False
　　　　　　End Select
　　　　End With

　　　　'コピーした数式セルをコピー先シートへ貼り付け（コピー実行可の時）
　　　　If bCopy = True Then
　　　　　　.Paste Destination:= _
　　　　　　　　.Range(.Cells(SHlast + 1, 2), .Cells(SHlast + 1, 171))
　　　　End If

　　　　'ここでシート保護を再設定
　　　　.Protect UserInterfaceOnly:=True
　　End With

　　'切り取り＆コピーモードをリセット
　　Application.CutCopyMode = False
　　'表示更新の抑止解除
　　Application.ScreenUpdating = True

End Sub
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

以上です。

お礼

御指摘があった通り、CopyメソッドとPasteメソッドの間で、
Protect関係のメソッドを操作しないように書き換えたら
うまくいきました！

Pasteメソッドのエラーでしたので、
コピーの範囲や、貼り付けの範囲の選択が違うのかな
と思ってそこらばかりをいじっていましたが、無事できました！

ありがとうございました。