【VBA】シートのコピー ～ 値に直す

質問

大変お世話になります。
VBAのコードについてご教示いただけませんでしょうか。

■やりたいこと
--------------------------------------------------------------------------------
(1) 【原紙】Sheetを、同ブック内の新規シートへコピー
(2)　新規シートのシート名を、［セル：B5］の値に変更
(3)　新規シートにコピーされてきた数式を値に変更

■作成してみたコード
--------------------------------------------------------------------------------
Sub SheetCopy1()
Worksheets("【原紙】Sheet").Copy Before:=Worksheets("【原紙】Sheet")
ActiveSheet.Name = Range("B5").Value

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub
--------------------------------------------------------------------------------

本日初めてVBAに触れた者が、見よう見まねで『■やりたいこと』を並べたコードのため、やはりエラーになってしまいます。

上記は、どこをどう直せばよろしいでしょうか。
もしくは、そもそも間違っておりますでしょうか。

ご教示いただきたく、何卒よろしくお願いいたします。

◎もし可能でしたら、併せてご教示ください◎
----------------------------------------------------------------------------
(1)の動作をさせるために、【原紙】Sheetの任意の場所に”ボタン”を設置するのですが、新規シートにもコピーされるため、そのコピー側のボタンを削除できたら…とも考えていますが、そういう動作も可能でしょうか。

回答No.4

kkkkkm · ベストアンサー

ActiveSheet.Shapes.Range("Z:AR").Delete
Shapesは図形等のオブジェクトの時に使います。
列でしたら
ActiveSheet.Range("Z:AR").Delete
もしくは(Columnsは列のことです)
ActiveSheet.Columns("Z:AR").Delete

先の回答ですがボタン一個だけでしたら
ActiveSheet.Shapes("Button 1").Delete
なのですが、複数の場合に
ActiveSheet.Shapes.Range(Array("Button 1", "Button 2")).Delete
とかになります。マクロの記録でやると一個だけでも多少違いますが先の回答のようになります。滅多にやらないことは自分で書くより記録して多少直すことが多いので、先の回答の削除部分もその要領で取り込んでいます。

お礼

kkkkkm 様
大変お世話になります。
回答No.6 と併せて拝見しました。
ご教示いただいた通りに実行し、無事に解決することができました。

モジュールについても、全く知らずに実行しておりました。
ご説明で大変よく理解できました。
重ね重ね、ありがとうございました。

今後も何かと質問させていただくかと思いますので、お時間許されましたら是非よろしくお願い申し上げます。

回答No.7

masnoske

■やりたいこと
--------------------------------------------------------------------------------
(1) 【原紙】Sheetを、同ブック内の新規シートへコピー
(2)　新規シートのシート名を、［セル：B5］の値に変更
(3)　新規シートにコピーされてきた数式を値に変更

これを次のように考えてみてはどうでしょうか。
(1)【原紙】Sheetの前に新規シートを追加
(2)　新規シート名を、【原紙】Sheetの［セル：B5］の値に変更
(3)【原紙】Sheetの全セルを新規シートに値で貼り付け

これならば、ボタンはコピーされません。

お礼

masnoske 様
お世話になります。ご回答、ありがとうございます。
仰る通り、この方法ならボタンはコピーされずに済みますね。

今回はVBAで対応したかったため、他の方にご教示いただいたコードで無事に解決することができました。お騒がせしました。

回答No.6

kkkkkm

なお、エラーになった原因はたぶんコードをシートモジュールに記載したためだと思います。標準モジュールに記載するとエラーにはならないと思います。

シートモジュールに記載すると
Cells.Select(これだけに関わらずシートの指定のないセルの指定は全て)
はコードが記載されているシートにたいして行われるのですが、Selectはアクティブではないシート(この時点でアクティブシートは新規シートです)では実行できずエラーになります。

なので ActiveSheet.Cells.Selectにし、現在アクティブなシートをSelectの対象として指定することでエラーを回避します。

一方、標準モジュールに記載されたコードのシートの指定のないセルの指定は、全てアクティブなシートを対象にします。

これも気を付けないと、シート1で実行したいコード (シートの指定なしセル指定あり)を、シート1以外がアクティブな状態で実行すると、アクティブなシート(たとえばシート2)のデータがぐちゃぐちゃになったりします。

回答No.5

mdmp2

NO.2 です。
デバッグで、どの行（コード）でエラーになったか、どのようなメッセージが出たか、教えて下さい。
B5 に入力したシート名を単純なものにして試行してください。

お礼

mdmp2 様
大変お世話になります。
引き続きのご回答、誠にありがとうございます。

エラーの原因ですが、標準モジュールへ記載すべきところをシートモジュールへ記載してしまっていたことでした。大変お騒がせしました。
ご教示いただきましたデバック、活用しております。

またいろいろと質問させていただくと思いますので、お時間許されましたら是非よろしくお願い申し上げます。

回答No.3

imogasi

コピー元のシート名をSheet1とする例（存在不存在のチェックは略）
できるだけシート名をWorksheets(XXX)で、具体的に記述しました。
Sub SheetCopy1()
Worksheets("Sheet1").Copy Before:=Worksheets("Sheet1")
sn = Worksheets("Sheet1").Range("B5").Value
ActiveSheet.Name = sn
Worksheets(sn).Cells.Copy
Worksheets(sn).Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
参考　シート名の命名ルールに注意。
https://ameblo.jp/test-engineer-kaz/entry-12324800508.html
Excel2010のセルシート名につけられる名前の制限

お礼

imogasi 様
お世話になります。
前日の質問に引き続き、ご回答ありがとうございました。
具体的なシート名の記載も、シート名のルールも、大変勉強になりました。

これから少しずつ、マクロについて勉強してまいります。
また機会がございましたらよろしくお願いいたします。

回答No.2

mdmp2

Excel 2019 で試しました。
【原紙】Sheet の前にB5 に記述した名前のシートが作成されました。
コードには問題がないと思います。
B5 に全角で　シート１　と記述し、他のセルはすべて空白で試しました。
可能性としては、B5 に入力した名前がシート名として使えないとか、各セルに記述された内容に問題があるとか、
マクロをデバッグモードで実行してみてください。どのコードで引っかかるかわかります。
デバッグモードで実行するには、開発タブのコードグループにある、Visual Basic をクリックしてエディターを開き、
マクロが記述されたモジュールを開き、
マクロの先頭、Sub SheetCopy1()　をクリックして、
デバッグタブの「ステップイン」をクリックします。クリックするたびに1行ずつコードが実行されます。
問題がある場所で何らかのメッセージが出ます。

補足

mdmp2 様
大変お世話になります。この度はご教示いただきまして、ありがとうございます。
動作の確認もしていただき、誠に恐縮です。

『デバッグモード』の実行での確認の件、大変勉強になりました。
ありがとうございました。

◎以下で追加の質問を入れさせていただいた『ボタン削除』の件ですが、
『ボタン』ではなく『列［Z～AR］』を範囲指定して削除する方法を思考錯誤中です。
（　Range("Z:AR").Delete　と追記してデバックモードで確認しましたが、
　どこの、どういうエラーかが理解しきれませんでした）

もしよろしければで結構ですので、上記につきましてもご教示いただけますと大変ありがたく存じます。
何卒よろしくお願い申し上げます。

回答No.1

kkkkkm

以下でいけると思いますよ。ボタンはフォームのボタンで
Button 1は「検索と選択」の「オブジェクトの選択と表示」で確認してください。

Sub ボタン1_Click()
Worksheets("【原紙】Sheet").Copy Before:=Worksheets("【原紙】Sheet")
ActiveSheet.Name = Range("B5").Value

ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Shapes.Range(Array("Button 1")).Delete
End Sub

補足

kkkkkm 様
大変お世話になります。
こちらでもご教示いただきまして、ありがとうございます。

ボタンの件もありがとうございます。
ちなみにですが…　『ボタン』ではなく、連続する複数列［Z～AR列］を指定して削除することは可能ですか？

値に置換部分以下を
-----------------------------------------------------------------
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Shapes.Range("Z:AR").Delete
End Sub
-----------------------------------------------------------------
へ置換してみましたがうまくいきませんでした…。

もしよろしければ、引き続きのご教示を何卒よろしくお願いいたします。