同ブック内でシートを追加したときにシート名と連番が

質問

WindowsXP EXCELL2003
いつも御世話になります。
何とか自分で作りたく色々調べてトライしましたが分からなくなりご教授を願う事になりよろしく御願いします。

仰ぎたいのは
納品書と請求書が一緒になった　・シート名「納請書１」、　がA4で１枚になったシートがあります。
とりあえずこの納請書なるものは３シート用意しています。
受注が追加されるごとに「納請書３」のシートコピーをして「納請書４」その都度追加します。
これをマクロの実行とか何かの方法で追加することは可能でしょうか。
もし可能ならばいい方法をご指導いただけませんでしょうか。

取り合えず１例で下記のマクロを
Sub シートの追加とプロパティ操作()
Dim NewWS As Worksheet

Set NewWS = Worksheets.Add(After:=Worksheets("納請書3"))

With NewWS
.Name = "納請書4"
.Columns.ColumnWidth = 20
End With
End Sub

このやり方は私か調べた物でトンチンカンかも知りません。

「マクロの実行」
Sheet3シートの追加とプロパティ操作
で「実行」すると次のように出ます。
「インデックスが有効範囲にありません」

回答No.3

xls88 · ベストアンサー

下記のように2行に纏まります。

Sub シートの追加とプロパティ操作()
Sheets("納請書3").Copy After:=Sheets("納請書3")
ActiveSheet.Name = "納請書" & Worksheets.Count
End Sub

シート構成が
納請書1 納請書2 納請書3 納請書4 ・・・・・
というように、連番に欠けがない、ということが前提です。
連番に欠けができると、シート名が重複する、といった事態が発生します。
シート名の重複をチェックする。
シート名から数字を抜き出し、その数字が最大値のものに、1を加えたシート名にする。
といった操作が必要かも知れません。

お礼

誠に有難うございました。
何回かのテストをしても問題なく思うとおりでした。
私のわがままをご指導いただき有難うございました。
今後ともご指導よろしく御願いします。

回答No.2

xls88

提示されたコードでは、新規シートを追加していますが、シートの複製を作るようにすればどうでしょうか。

「納請書3」のシートラベルを右クリック、「シートの移動またはコピー」で、「コピーを作成する」にチェックを入れて「OK」
その後、コピーされたシートの名前を変更
と、いった操作で「納請書3」の複製が出来ます。
上記の操作を「マクロの記録」すれば参考コードが得られます。
必要な部分のみ書き出すと
Sheets("納請書3").Copy After:=Sheets(3)
Sheets("納請書3 (2)").Name = "納請書4"
となります。
Afterパラメーターのところで、インデックスが使われていますが、シート名にした方が確実です。
Sheets("納請書3").Copy After:=Sheets("納請書3")

シート名の連番をつける部分は
Worksheets.Count でブック内のワークシートの数が得られます。
シートの構成が、納請書1 納請書2 ・・・・・と連番になっているようですから、
今回の例なら、”納請書”にワークシートの数をくっつければ良いと思います。
NewWS.Name = "納請書" & Worksheets.Count
といった感じになります。

以上の内容で、コードを書いてみてください。
上手くいかなければ、コードを提示して、問題点を教えてください。
エラーがでるなら、エラー内容も教えてください。

補足

下記のようにマクロを入れテストしました。

Sub シートの追加とプロパティ操作()
Dim NewWS As Worksheet
Sheets("納請書3").Copy After:=Sheets(3)
Sheets("納請書3 (2)").Name = "納請書4"
Sheets("納請書3").Copy After:=Sheets("納請書3")
With NewWS
NewWS.Name = "納請書" & Worksheets.Count
End With
End Sub

１　「納請書3」のコピーはでき「納請書3Z(2)」は追加されました。
　　「納請書4」とシート名を変更しました。
２　　又引き続き「納請書3」のコピーを　マクロ実行　すると
　　 　下記のように、
　　　オブジェクト変数または with ブロック変数が設定されて
　　　いません。
３　　続けると又下記のように、
　　　Microsoft Visual Basic 赤丸にX　で４００

どこかが私のやり方に不具合があるのでしょうか。
誠に恐れ入りますが再度ご指導いただけませんか。
よろしく御願いします。

回答No.1

xls88

シート名の数字の文字種が違っていたりしませんか？
例えば、本文では全角で書かれていますが、コードの中では半角になっています。
あるいは漢字と数字の間にスペースがあるとか、、、

Set NewWS = Worksheets.Add(After:=Worksheets("納請書3"))
を
Set NewWS = Worksheets.Add(After:=Worksheets("納請書３"))
とすればどうなるか試してください。

補足

納請書3　のプロパティで「名前」がおっしゃる通り全角の３になっていました。
半角に修正したら　納請書3　のコピーでなく普通のシートのコピーができました。

ご指導いただけませんか。
１　数式などが入った　「納請書3」　のコピーするのはどういう変更すればいいですか。
２　「納請書3」のコピーで「納請書5」　「納請書6」と増やしてゆきたいのですが。