【Excel VBA】書き込み先のシートが存在しなかった場合、新規でシート作成する方法

あるブックのマクロを実行すると、別のブックの指定したシートにセル範囲のデータを値で貼り付ける処理をしていますが、
指定したシートが別のブックに存在すればそこに貼り付けますが、
無かった場合、新規でシートを作って貼り付ける様にするにはどうしたら良いでしょうか？

例えば、
元ブック.xlsm から書き込み先.xlsx のBシートに書き込む設定になっていて、

書き込み先.xlsx には、
Aシート、Cシート、Dシート　で構成されていたら、Bシートを新規で作成し、
Aシート、Bシート、Cシート、Dシート　になるようにしたいです。
※実際にはA、B、C、Dと並ぶわけではないので末尾に追加とか順不同で構いません。

何故なら、元ブック1.xlsm、元ブック2.xlsm・・・と複数あってそれぞれが実行しますが、
これなら、予め全てのシートを準備しておかなくても、全て収集できるようになるからです。
お分かりの方、よろしくお願いいたします。

No.3 ベストアンサー 回答者： fujillin 回答日時： 2021/11/04 23:32

こんばんは

方法としては、概ね二通りかと思います。
1）シートを指定して、エラーが発生したら、新規で作成する
2）存在するシートをチェックして、無ければ新規で作成する

1）は、エラー処理として記述すれば良いでしょう。
https://docs.microsoft.com/ja-jp/office/vba/lang …

以下は、2）の方法の例で、関数として作成してみました。
「シート名」と「ブックオブジェクト」を引数として渡せば、対象シートを返す関数です。
（該当するシートがあればそのシートを、無ければ新規に作成して返します）
※ 対象とするブックは、開いている状態であると仮定しています。

Function getSheet(ByRef sheetName As String, ByRef bk As Workbook) As Worksheet
Dim sh As Worksheet
For Each sh In bk.Worksheets
If sh.Name = sheetName Then Exit For
Next sh

If sh Is Nothing Then
Set sh = bk.Worksheets.Add(Type:=xlWorksheet)
sh.Name = sheetName
End If
Set getSheet = sh
End Function

この回答へのお礼

ありがとうございます。
凄く良さそうだとは思うのですが自分の知識が低すぎて試すに至りません。
Call getSheet() の引数の記述が分からないんです。
具体例を出してもらえると助かります。

お礼日時：2021/11/05 12:16

No.2 回答者： めぐみん_ 回答日時： 2021/11/04 23:25

自Ｂｏｏｋの場合についてまずやってみては？

Dim ws As Worksheet

On Error Resume Next

Set ws = ThisWorkbook.Worksheets("Ｂシート")

On Error GoTo 0

If ws Is Nothing Then

With ThisWorkbook

Set ws = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
ws.Name = "Ｂシート"

End With

End If

この回答へのお礼

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

お礼日時：2021/11/08 23:23

No.1 回答者： nicotinism 回答日時： 2021/11/04 22:40

存在しないシートを選択しようとすると

Sheets("あれ").select
実行時エラー９ インデックスが有効範囲にありません
となるので、これでエラー分岐してシートが無ければ加えれば良いのでは。
Sheets.add ・・・・
Excelなら開発タブにマクロの記録が有りますので試してみてください。

この回答へのお礼

ありがとうございます。
素人なのでこんな質問で恐縮ですが、
エラー分岐するのは、
ON ERROR GOTO～　文を使うという事で合ってますでしょうか？

エラーじゃなければ、存在するシートに、データを貼り付けて、
エラーだったら、まずシートを作成してから、データを貼り付ける、と。

なので、「データを貼り付ける」処理は2回書かないといけないのかな、というのが素朴な疑問です。

お礼日時：2021/11/05 11:40