ExcelVBAにシートに名をつけて保存のさせたい

質問

現在、VBA勉強中の初心者です。

自分なりに作成したVBAではうまく作動しません。
どなたか教えてください。

sheet１（受付簿）のデータをB３～U４３までコピーして、名前をつけて新たに別シートに作成保存し、作成したシートのA１に、値と書式を貼り付けます。
そして作成したシートを、一番右側に作成したいのですが、エラーが発生します。

（エラー）
「実行時のエラー
オブジェクトは、このプロパティまたはメソッドをサポートしていません」
が発生します。

下記のように、VBAを作成したのですが、どこが間違いなのかご指摘をお願いします。

Sub 別シートに名前をつけて保存()
Dim title As String
Dim msg As String
Dim t As String

title = "　別シートに名前をつけて保存する"
msg = "名前を入力してください"
t = InputBox(msg, title)

Worksheets("受付簿").Range("B1:U43").Copy

With Worksheets.Add
.Name = t
.Range("a1").Select
.Range("a1").Paste
.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With

Application.CutCopyMode = False

End Sub

お忙しい中、申し訳ありませんが、どうぞよろしくお願いします。

回答No.3

keithin · ベストアンサー

＞シートに名前をつけて保存した後、VBEを見たらマクロまで一緒にコピーされております。

一般論として，ブックにマクロを登録するときには
１．ALT+F11でVBE画面を開く
２．マクロを付けたいブックのVBAProjectに（通常は何もしなくてもそうなっていますが）
　　VBE画面の挿入メニューから標準モジュールを追加する
３．挿入した標準モジュールにふつーのマクロを記述する
４．Excel画面に戻って登録したマクロを実行する
のように作成します。

追加ご質問の意図は，例えば「シート名タブを右クリックしてコードの表示を選んで表示されたシートに回答マクロを記入して動かしてます」といった状況だということでしょうか。
シートモジュールには回答したような一般的なマクロじゃなく，シートのイベントプロシジャだとかコントロールツールボックス用のコマンドボタンクリックのマクロだといった，特殊な用途のマクロを記述するために使います。

ご質問で作成中のものや既に寄せられている回答のようなマクロを使いたいときは，シートモジュールに記入したマクロは削除して（結果してシートはクリーンになります）上述手順で標準モジュールにマクロを書き改めてください。

お礼

keithin　様　　myRange様

本日は本当にありがとうございました。
おかげさまで、１００％自分がイメージしたものが出来上がりました。

またシートモジュールと標準モジュールの区別も一緒に教えてもらい、大変参考になり
ました。

今まで、自分なりに勉強してきて、これは使えそうかな・・と思ったものを、コピーして
貼り付けたりして、マクロを作成してきたのですが、今回のように検索しても自分が思う
ようなマクロを書いてあるページなど無く（あたりまえですが）、まったくもって応用が
利かないと実感しました。
やはりマクロは体型的に勉強するべきだな・・・と改めて反省いたしまいした。

長くなりましたが、本日は本当にありがとうございました。

感謝！！！

回答No.4

imogasi

エラーが出る行も参考のため、質問に書いておくものだ。
＞名前をつけて新たに別シートに作成保存し
この表現は紛らわしい
「名前をつけて新たに別シートに作成し」で良いだろう。最後にこのBookを保存するとしても、質問事項では無いだろう。
質問のコードにも無い。
＞一番右側に作成したいのですが
このコードは質問のコードには無いのでは。
ActiveSheet.Move after:=Sheets(Sheets.Count)
Sheet1にしたのは、回答者がテストデータを作りやすいからだ。ここまで気を使ってくれればありがたいが。
データ例を特徴の在る部分を十行程度書いてくれるとテストにはありがたいが。
ーー
コード
下記でどうかな。
Sub 別シートに名前をつけて保存()
Dim title As String
Dim msg As String
Dim t As String
title = "　別シートに名前をつけて保存する"
msg = "名前を入力してください"
't = InputBox(msg, title)
t = "aaa"
'Worksheets("受付簿").Range("B1:U43").Copy
Worksheets("sheet1").Range("B1:D15").Copy
Worksheets.Add.Name = t
ActiveSheet.Range("B1").Select
ActiveSheet.Paste
'.Range("a1").Paste
'.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
':=False, Transpose:=False
'.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
'SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Move after:=Sheets(Sheets.Count)
End Sub
もうエラーが起こる議論の余地が無いコードの部分は、コメント化してt = "aaa"
のようにしてテスト・デバッグ対象から省くことをお奨めする。
ーー
コピーについて
セル範囲のコピー貼り付け、
シート全体をシートへのコピー貼り付け
のVBAは十分勉強してから使うこと。初心者には難しい点だ。怖さを知らないね。
今後、出来ればセル範囲の値の代入だけで出来るだけ済ますことをお奨めする。
またDestinationを活用すると良い。
＞値と書式を貼り付けます。
これ以外のセルの属性は移ると困るのために、こんな思わせぶりな表現になっているのか。
コピー貼り付けではダメなのか。意識しているなら、理由を明示のこと。
ーー
以下はGoogleででも照会すれば記事がたくさんある事項だ。これらを調べましたか。
シート名の重複チェックは、上記コードには加えてないが
http://gogo-blog.jugem.jp/?eid=22
を読んで勉強のこと。1発（1行）でチェックすすコードは無いようだ。VBSなどBook名ではFileExistsが使えるものもあるが、Sheet名には無い。
ーー
上記テストデータには、セル結合を含めてテストした。大丈夫のようだ。
セル結合のあるシートの扱いにくさは
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_030_200.html
など参考に
ーーー
シートの一番右に
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_sheet.html
の「シートを追加する」　参照

お礼

imogasi　様

ご回答ありがとうございました。
マクロ初心者とはいえ、質問の仕方が悪いところが多々あり、ご迷惑をおかけいたしま
した。
今後は、教えていただいたサイトも参考にさせていただき、勉強していきたいと思います。

お忙しい中、回答を頂きありがとうございました。

回答No.2

keithin

Sub Sample1()
　Dim title As String
　Dim msg As String
　Dim t As Variant

　title = "　別シートに名前をつけて保存する"
　msg = "名前を入力してください"
　t = InputBox(msg, title)

　Worksheets("受付簿").Copy after:=Worksheets(Worksheets.Count)　'最後に
　With ActiveSheet

　　.UsedRange.Value = .UsedRange.Value
　　.Range("A:A").Delete shift:=xlShiftToLeft

　　On Error GoTo Errhandle
　　.Name = t
　　On Error GoTo 0
　End With
　Exit Sub

Errhandle:
　msg = "指定のシート名 '" & t & "' は使えません。再入力"
　t = InputBox(msg, title)
　If t = "" Then Exit Sub
　Resume
End Sub

＃上手く行かない困っている状況を詳しく話して貰うことも勿論必要ですが，それを具体的にどのような姿に解消したいのかについても補足が必要です。

お礼

回答者：keithin　様

回答誠にありがとうございます。

＞＞＃上手く行かない困っている状況を詳しく話して貰うことも勿論必要ですが，それを
具体的にどのような姿に解消したいのかについても補足が必要です。

すみません。うまく説明出来なくて、回答者：myRange　様まかせで再質問して
おりました。

keithin　様作成のVBAが自分が求めていた一番の理想の形ですが、一つ気になる点が
あります。
シートに名前をつけて保存した後、VBEを見たらマクロまで一緒にコピーされております。

このままでも、特に支障は無いと思うのですが、シートが最大で７０になる可能性
があるので、要領が大きくなりすぎしてしまうような気がします。
可能であれば、データ（書式を含む）だけをシートに保存出来ないでしょうか？

回答No.1

myRange

>ExcelVBAにシートに名をつけて保存のさせたい

この"保存"は、ブックの保存ではないですよね？？
提示のコードには、それがないので。

で、提示のコードを修正すると。。。

'---------------------------------
Sub 別シートに名前をつけて保存()
　Dim title As String
　Dim msg As String
　Dim t As String

　title = "　別シートに名前をつけて保存する"
　msg = "名前を入力してください"
　t = InputBox(msg, title)

　If t = "" Then Exit Sub

　With Worksheets.Add(after:=Sheets(Sheets.Count))
　　　　.Name = t
　　　　Worksheets("受付簿").Range("B1:U43").Copy .Range("A1")
　End With

'▼保存するとき▼　ThisWorkbook.Save True
End Sub
'------------------------------------------------

実務でやる場合は、入力したシート名が既に存在する場合のエラー処理コードが必要でしょう。
以上です。

お礼

回答者：myRange　様

ご回答誠にありがとうございます。
＞＞この"保存"は、ブックの保存ではないですよね？？
はい、myRange　様が作成していただいた、VBAを実行したかったのです。

ただ、ご指摘の点を含めて２点ほど再度質問させてください。
１　Worksheets("受付簿")には、セルで結合されたものなどがあり、そのまま新しいシート
　　に貼り付けると、新しく作成されたシートには、書式などがまったく異なります。

２　＞＞実務でやる場合は、入力したシート名が既に存在する場合のエラー処理コードが必
　　要でしょう。
　まったくもってその通りです。いざ、同じ名前で実行したら・・・

以上、２点の問題点があります。大変、勝手なのですが、対処方法を教えてくださいませんでしょうか？