ExcelVBAでマクロを含むブック内の特定のシートをコピーすると特定のシートにマクロがうつってしまう

いつもお世話になっております。
ExcelVBAで、簡単な処理をしたいのですがマクロがあるためか、なぜかうまくいかず困っております。
また、エラーについても解決方法を色々とググったのですがなかなか見つからず、それでテラテイルに聞きました。よろしくお願いいたします。

【やりたいこと】

マクロを含むブックの特定のシートを（マクロなしで）新規ブックに保存したい

問題点

新規ブックに保存はできたが、デバッグ中、よく見るとマクロ実行中の名前が新規ブックの名前になっている。

更に、この状態でデバッグをしながらコードを修正しても元のブックに保存されない。

しかし、実行後出来上がった新規ブックを確認すると、マクロは存在していません。
更に、修正したはずのマクロも元ファイルにも保存されていません。

これではデバッグしながら修正ができないのですが、何か良い方法はありますでしょうか？

他の方法

色々とググってみると、「SaveAs」ではなく「SaveCopyAs」の方がいいという書き込みもありました。
しかし、その方法だと「メソッドまたはデータメンバが見つかりません」と出ます。

SaveCopyAsの構文は、
「Workbookオブジェクト.SaveCopyAs (フルパス＋ファイル名)
なので、「filename」にフルパス名とファイル名を格納したものを使ったのですが・・。

何かほかに良い方法はありますでしょうか？
また、同じような経験をしておられる方はいますでしょうか？

すみませんが、知恵をお貸しください。
よろしくお願いいたします。

行動規範の内容に同意します

回答4件

私は開発タブからVisual Basicをクリックしてマクロ編集画面を開いています。
左側の上側が「プロジェクト- VBAProject」となっています。
そこに開いているexcelファイルが「VBAProject(ファイル名)」という形でツリー形式で出ています。
このVBAProjectは開いているexcelファイルの数だけあります(マクロあり/マクロなしの両方)。
この下に「Microsoft Excel Objects」と「標準モジュール」があります。但し標準モジュールはマクロありのファイルの下にしかありません。
前者はexcelのシートをその下に含んでおり、後者がマクロのソースを含んでいます。
標準モジュールの下に「Module1」があるはずです。そこをダブルクリックするとソースが表示されます。
ここを編集し、保存すると元のマクロを含んだexcelファイルに修正したマクロが保存されると思います。

投稿2020/03/12 07:19

sage

総合スコア1240

退会済みユーザー

2020/03/25 01:24

なるほど、そういうからくりがあったのですね。色々と説明して下さって感謝しております。随分参考になりました。

行動規範の内容に同意します

これではデバッグしながら修正ができないのですが、何か良い方法はありますでしょうか？

そういうものだと割り切って、実行前にマクロを含むファイルを保存→実行後にマクロを編集したいときはまた開きなおす、じゃダメですか。

色々とググってみると、「SaveAs」ではなく「SaveCopyAs」の方がいいという書き込みもありました。
しかし、その方法だと「メソッドまたはデータメンバが見つかりません」と出ます。

SaveCopyAsはワークブックのメソッドです。
変数名を見るとwsMainになっていますが、これはワークシートではないでしょうか？
定義箇所が見えないので推測ですが。

投稿2020/03/12 07:45

ttyp03

総合スコア17000

退会済みユーザー

2020/03/25 01:23

SaveCopyAsはワークブックのメソッドだったのですね、こちらが間違っておりました。ご指摘ありがとうございました。

行動規範の内容に同意します

内容がほかの方と同じでしたので取り下げます

投稿2020/03/18 06:41

編集2020/03/18 06:43

h.horikoshi

総合スコア505

ベストアンサー

考え方？手順？がおかしいですね。

シートを新規ブックにコピー　→　それを名前を付けて保存
かな。

あるいは、現状のコードを生かすなら、
自ブックを開きなおすように予約して、
一旦保存せずに閉じるでもいいかもですが。。。。

ExcelVBA
1Sub test()
2    Dim s As String
3    With ThisWorkbook
4        'シートを新規ブックにコピー
5        .Worksheets(1).Copy
6        s = .FullName
7        s = Replace(s, .Name, "テスト.xlsx")
8    End With
9    '今開いたブックを名前を付けて保存
10    Workbooks(Workbooks.Count).SaveAs s, xlOpenXMLWorkbook
11End Sub