エクセルでシートを挿入の際

質問

エクセルでシートを挿入すると
sheetの後に続く番号が一ずつ増えていきますよね
その後でシートの名前を変更したり　別のシートを
削除して　また挿入しても番号は増え続けていきますが
そういうことがないように
マクロでの記述で
「シート名を指定して　新規シートを追加
　（挿入）」
するにはどのような記述をしたら
よいでしょうか？

回答No.1

boy_boy · ベストアンサー

こんにちは。

標準モジュールに

Sub Test()
Worksheets.Add(after:=Worksheets _
(Worksheets.Count)).Name = "ennkai"
End Sub

をコピペして「Test」を実行すると

シート名「ennkai」と云う新規シートが
ファイルの最後尾に挿入されます。

「"」と「"」で挟まれた部分に任意のシート名を
指定します。

※変数を指定したり、セル参照したり「挿入位置」の
　指定も出来ますが、取り合えず・・・。

お礼

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

シンプルな記述で私にも
大変わかりやすかったです

勉強になりました

回答No.4

Wendy02

misatoanna　様のコードと、同じ発想ですが、私の場合は、シート番号の最大値を取るものです。シートの数字の枝番は、親番号のみで下の数字は無視されます。
飛び番号 1,2,4 の場合は、3 ではなく、5になります。

'<ThisWorkbook登録のみ>
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim ShNum As Variant
Dim shMaxNum As Integer
Dim i As Integer
With ThisWorkbook
　For i = 1 To .Sheets.Count
　 If .Sheets(i).Name Like "Sheet#*" Then
　　ShNum = Replace(.Sheets(i).Name, "Sheet", "")
　　If shMaxNum < Val(ShNum) Then
　　 shMaxNum = Val(ShNum) 'シート番号の最大値
　　End If
　 End If
　Next i
　If Sh.Name <> "Sheet" & CStr(shMaxNum + 1) Then
　 Sh.Name = "Sheet" & CStr(shMaxNum + 1)
　 .Sheets(Sh.Name).Move After:=Sheets(Sheets.Count)
　End If
End With
End Sub

回答No.3

misatoanna

> シートの名前を変更・削除してまた挿入しても番号は増え続けていきます
> シート名を指定して新規シートを追加

私からも稚拙なマクロ(テンプレートにして使っています)の例です。
シート名は指定できませんが、「"Sheetxx"形式で、重複しない最も小さい番号の
シート名」で最後尾に追加します。

[Alt]を押しながら[F11]を押すと Visual Basic Editor が開きます。左端に表示
されている ThisWorkbook をダブルクリックして、右側の画面に下記をコピペして
から、Visual Basic Editor を閉じます。
'
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim i, j
ActiveSheet.Move After:=Sheets(Sheets.Count)
i = 1
LP:
For j = 1 To Sheets.Count - 1
If "Sheet" & i = Sheets(j).Name Then
i = i + 1
GoTo LP
End If
Next j
ActiveSheet.Name = "Sheet" & i
End Sub
'
シートを挿入すると自動的に処理されます。

回答No.2

matsu_jun

こんなのはいかがでしょう。実行するとシート名を尋ねるボックスが表示されます。
標準モジュールに以下を貼り付けます。

'ここから------------------------------------------------------------------------------------------
Sub シート名を指定して追加()
　　On Error Resume Next
　　Do
　　　　Err.Clear
　　　　ShNam$ = InputBox("追加したいシート名を記入", "シート作成", "Sheet" & Worksheets.Count + 1)
　　　　If ShNam = "" Then Exit Sub
　　　　Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = ShNam
　　　　If Err <> 0 Then
　　　　　　Application.DisplayAlerts = False
　　　　　　Worksheets(Worksheets.Count).Delete
　　　　　　Application.DisplayAlerts = True
　　　　　　MsgBox Err.Description
　　　　End If
　　Loop While Err <> 0
End Sub
'ここまで------------------------------------------------------------------------------------------

「標準モジュールに貼り付ける」方法がわからないときは、以下の方法でお試しを。

1) ツール－マクロ－マクロの記録 で出てきたウィンドウの、「マクロ名(M)」と書かれた欄に、
　　「シート名を指定して追加」と書き込んで、「OK」をクリックしてください。
2) 画面に、「■」の描かれたボタンと、何か表みたいな絵の描かれたボタンの2つのボタンだけの
　　ツールボタンが表示されるので、「■」のボタンを押します。
　　これでマクロの記録が終了します。つまり何も記録せずに終了したことになります。
3) AltキーとF8キーを同時に押して「マクロ」ウィンドウを開きます。
4) 「シート名を指定して追加」を選択し、右の「編集(E)」ボタンをクリックしてください。
5) 「Microsoft Visual Basic」というウィンドウが新たに立ち上がり、
　Sub シート名を指定して追加()
　　'
　　' シート名を指定して追加 Macro
　　' マクロ記録日 : 2005/5/7 ユーザー名 : ○○ ○○
　　'

　　'
　End Sub
と表示されているはずです。
6)このSub 担当別シート作成()と書かれている行と、End Subと書かれている行の間に、
　　上の「ここから」から、「ここまで」を貼り付けます。
　　(実際は、一番上の行と、一番下の行が同じことが書いてあってかぶっているので、
　　一番上の行と一番下の行はコピーしなくても結構です)
7) Microsoft Visual Basicウィンドウを閉じます。
8) 後はマクロを実行するのみ。

お礼

回答ありがとうございました

すでに存在するシート名を入力すると
エラー回避まで記述してくださって
マクロ勉強中の私には大変勉強になります！

また何かありましたらよろしくお願いします