エクセルのVBAでシート名が重なるときの処理

質問

お世話になります。
エクセルのＶＢＡにてリストボックスで選択した単語をシート名に
反映させるマクロを作成しました。

が、一度シート名を作ると２回目に同じ単語を選択すると、
デバック？画面になってしまいます。
『同じ名前のシート名は作れません・・・』

希望としては、同じ名前が出たら自動に連番が割り振られる
ようなものを希望しています。

マクロの記録で確認しても、やはり同じデバック要画面がでます。
別シートにシート名を反映させて、同じ名前がヒットしたら
文字列を追加して、そのシートに反映し続ける・・・
ようなことは考えられますが、どうも不細工で気が向きません。

もっとスマートな考えがあれば教えていただきたく
よろしくお願いします。

参考に作ったＶＢＡを下記します。
これだと、途中でシートを削除してしまうと
デバック画面が発生してしまいます。
（マクロの切り抜きなので、
　リストで選択したものが反映されるマクロではありません）

Dim シート名 As String
Dim n As Integer

Sheets("伝票マスター").Select
Worksheets("伝票マスター").Copy before:=Worksheets("伝票マスター")

n = Sheets.Count
Sheets("伝票マスター (2)").Select
ActiveSheet.Name = "伝票" & n - 1
Range("D2") = n - 1
Range("D1").Select
、

回答No.5

Wendy02 · ベストアンサー

こんばんは。

こんな感じにしたらどうでしょうか。
On Error GoTo で処理するほうが良いのですが、それを発生させるために、Active やSelect を使うと、画面が動きますから、WorksheetFunction で値を取ります。On Error Resume Next ですと、値が確保できませんから、Variant 型の変数の中に、Null 値を入れる方法を取りました。Null値はワークシートでは発生しません。別にどんな方法でもよいのですが、シートの位置に関係なくシート名をチェックしないとうまくありません。

ブックに伝票マスターがない場合の処理を入れておきました。

'-------------------------------------------
Sub Test1()
　　Dim i As Integer
　　Dim ret As Variant
　　Dim n As String
　　Const SHN As String = "伝票マスター"

　　On Error GoTo ErrHandler
　　Worksheets(SHN).Select
　　For i = 1 To ActiveWorkbook.Worksheets.Count
　　　　On Error Resume Next
　　　　ret = Null
　　　　n = "伝票 - " & CStr(i)
　　　　ret = Worksheets(n).Range("A1").Value
　　　　If IsNull(ret) Then Exit For
　　　　On Error GoTo 0
　　Next i
　　Application.ScreenUpdating = False
　　Worksheets(SHN).Copy Before:=Worksheets(SHN)
　　With ActiveSheet
　　　　.Name = n
　　　　.Range("D2") = i
　　　　.Range("D1").Select
　　End With
　　Application.ScreenUpdating = True
　　Exit Sub
ErrHandler:
　　If Err.Number = 9 Then
　　　　MsgBox "アクティブブックには、" & SHN & "がないように思われます。", vbExclamation
　　End If
End Sub
'-------------------------------------------

お礼

ご回答、ありがとうございました。
また、返信が遅れまして、申し訳ございませんでした。

何とか、この構文を改良しながら達成することができました。
後は、この中身をよく理解し、自分のものにしたいと思います。

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

回答No.4

imogasi

選択したシート名が、今有るシート名と同じものが有るかチェックして、在れば別の名前にしたらしまいだと思うが、何が聞きたいのか。
ーー
（１）ダブっているかどうかのチェック方法？
（２）連番などをつける方法？
この点に関して＞文字列を追加して、の具体的な例を挙げないとダメだ。
（１）について
Existｓと言うような既製の関数は無い。自分でチェックするしかない。
http://exceltip.com/st/Determine_if_a_sheet_exists_in_a_workbook_using_VBA_in_Microsoft_Excel/485.html
のような例が載っている
普通はFor Each　　でチェックすると思う。
ーーー
添え字的な部分が、数字なら（数字付加が一番易しいかな）、リストボックスに有る文字列で見つかった文字列を空白で置換して削除して、残りの数字を＋１したものを付加して、シート名とする。などが思いつくが、たのばあいだとさらに面倒です。
現存するシートが集計表2ーーー集計表（リストボックスから）の部分を空白で置換ーー2が残る--2+1の3を付加ーー集計表3
もしこの線でやるとして、コード作成は大丈夫かな。

お礼

ご回答、ありがとうございました。
また、返信が遅れまして、申し訳ございませんでした。

皆さんのコメントを参考にしながら、何とか達成することができました。

（１）について知りたかったのですが、
何とか達成することができました。
コメント頂きありがとうございました。

回答No.3

fujillin

入力された名前で、無条件にシートを作成する前に、その名前のシートが既にあるかどうか調べればよいのでは？
（作成してみて、エラーだったらエラー処理、という方法もありますが）

例えば、以下の関数へ新しく付けたい名前を渡すと、既にある場合はTrueが返されます。
なので、Trueの間は附番を大きくしていって、OK（False)ならその名前を採用するとか…
Function testSheet(newName As String) As Boolean
　testSheet = False
　For cnt = 1 To Worksheets.Count
　　If Worksheets(cnt).Name = newName Then testSheet = True: Exit For
　Next cnt
End Function

なお、ご質問とは関係ありませんが、
　Worksheets("○○").Copy before:=Worksheets("○○")
で新しくできたシート名を設定するのなら、これに続けて
　ActiveSheet.Name = "△△"
みたいなほうが簡単だと思います。

お礼

ご返答ありがとうございます。

しかし、functionが何者かわからずに
ネットで数時間調べましたが、

(1)
sub（）
・・・
end　sub()
の次に続けて
Function testSheet(newName As String) As Boolean
・・・
End Function
と記述する。

(2)どうやら、newnameを変数とする
(1)のsub()の中でかぶる可能性のあるシート名を
newnameに代入する？？
↑
この辺りがよく理解できませんでした。
（実際にやってみると、デバック要のポップが出現しました。）

非常に恐縮ですが、追記いただけないでしょうか？

回答No.2

rukuku

こんばんは

選択した単語＋連番
というルールがあるならば、シート名を適用する前に事前チェックできます。

一番手っ取り早いのは、
On Error Goto
を使う方法です。同じシート名があれば、エラーになりますから、そのときはエラー処理のルーチンを実行して、次の番号を試してみます。

気の利いた処理としては、エラーにならないように事前にチェックする方法です。
最初の「シート名」を単に「単語」にするよりも「単語名(0)」と番号を振ってしまう方がプログラムはスッキリします（最初の番号は0ではなく1でも構いません。）

具体的なプログラムも「こんな感じ」くらいには頭の中にあるのですが、ちゃんと動くか検証してみてから回答します。

お礼

ご回答、ありがとうございました。
また、返信が遅れまして、申し訳ございませんでした。

皆さんのコメントを参考にしながら、何とか達成することができました。

ＯＮ　ｅｒｒｏｒ　ｇｏｔｏは、
ネットで調べたのですが、よく使い方がわかりませんでした。
他の構文を参考にこれから理解しようと思います。

回答No.1

kmetu

http://www2s.biglobe.ne.jp/~iryo/vba/VBA03.html
３ー２シートがあるかチェックしなければ追加する

を参考にされてはいかがでしょう。

お礼

ご回答、ありがとうございました。
また、返信が遅れまして、申し訳ございませんでした。

皆さんのコメントを参考にしながら、何とか達成することができました。