ExcelのVBAでシートのコピー

質問

Excel2000
Win2000

複数（最低５）のシートが存在するExcelのBookでExcelのVBAを使用して

strSheetName="XXX1"
intSheetNo = 1
Sheets(strSheetName).Copy after:=Sheets(intSheetNo)

とシートのコピーをしています。
この時コピーされたシートのインデックスが２にならないんですが、何故なんでしょ？

やりたい事はコピーしたシートの名前をExcelが勝手につけた名前から変更したいんです。
コピーされたシートのインデックスが２になると思っていたので上手く行きません。
Sheets(intSheetNo + 1).Name = "XXX1-1"

回答No.6

Wendy02

#1 のBLUEPIXY さんのテスト・コードを、もう一度、詳しくさせてみました。

Sub test()
Dim i As Long
Debug.Print "Index: Sheets.Name: ObjectName"
For i = 1 To Sheets.Count
　 Debug.Print Sheets(i).Index; " : "; Sheets(i).Name; " :　"; Sheets(i).CodeName
Next i
End Sub

※わざと、Sheet1 と　Sheet3 を入れ替えたブック。

ObjectName= CodeName = WorksheetName

挿入前
Index : Sheets.Name : ObjectName
1　: Sheet1 :　Sheet3
2　: Sheet2 :　Sheet2
3　: Sheet3 :　Sheet1
4　: Sheet7 :　Sheet7

挿入後　Sheet1-1 を挿入したら、

Index : Sheets.Name : ObjectName
1　: Sheet1 :　Sheet3
2　: Sheet1-1 :　Sheet4
3　: Sheet2 :　Sheet2
4　: Sheet3 :　Sheet1
5　: Sheet7 :　Sheet7

Indexは、間違いなく、2になります。作った順序ではありません。
当然、シート名は、きちんと並んでいるという条件になりますね。

＞シートのインデックスは視覚的に並んでいる順番ではなく
私は、それはみたとおりに並んでいる順序だと思いますね。

シート1 をシート3 の後ろに移動させたら。

Index: Sheets.Name: ObjectName
1　: Sheet1-1 :　Sheet4
2　: Sheet2 :　Sheet2
3　: Sheet3 :　Sheet1
4　: Sheet1 :　Sheet3
5　: Sheet7 :　Sheet7

この状態で、枝番シートを挿入したら、通常のコードでは、Index は、5 になりますね。

Index: Sheets.Name: CodeName
1　: Sheet1-1 :　Sheet4
2　: Sheet2 :　Sheet2
3　: Sheet3 :　Sheet1
4　: Sheet1 :　Sheet3
5　: Sheet1-2 :　Sheet4
6　: Sheet7 :　Sheet7

だから、結果的に、
＞strSheetName="XXX1"
＞intSheetNo = 1
＞Sheets(strSheetName).Copy after:=Sheets(intSheetNo)

Sheets(strSheetName)　←→ Sheets(intSheetNo)
とは、シート名と、Index とは、同時に扱えないわけです。

補足

お返事遅くなって申し訳ないです。
今日１日コード追ってました。

私の現状認識が甘く質問の仕方が悪かったみたいです。
ちょっと不安ですが結果的には解決しました。

状況

Index:SheetName:ObjectName
1　　:xxx1　　　:Sheet1
2　　:check1　　:Sheet3
3　　:Message 　:Sheet7
4　　:check2　　:Sheet4
5　　:work1　　 :Sheet2

SheetName「xxx1」をコピーして「xxx1-1」とする。
元々「xxx1-1」がある場合は「xxx1-2」とする。以下順に名前の枝番(-n)を増やす。
SheetName「xxx1」はそのまま残す。
コピーする対象シートはシート「work1」に記載してあり、複数ある場合がある。
シート「check1」「check2」「Message」「work1」は順番は決まっていない。

想定結果

Index:SheetName:ObjectName
1　　:xxx1　　　:Sheet1
2　　:xxx1-1　　:Sheet8
3　　:check1　　:Sheet3
4　　:Message 　:Sheet7
5　　:check2　　:Sheet4
6　　:work1　　 :Sheet2

の予定が
実際の結果

Index:SheetName:ObjectName
1　　:xxx1　　　:Sheet1
2　　:xxx1-1　　:Sheet3
3　　:Message 　:Sheet7
4　　:xxx1(1)　 :Sheet8
5　　:check2　　:Sheet4
6　　:work1　　 :Sheet2

となり「xxx1(1)」が残り「check1」のシート名が変わってしまっていました。

色々調べてみたら、最初の状態の時にIndexの２～５が非表示(Visible=False)となっており
コードが実行されると「Message」シートが表示され他のシートはすべて非表示と
なっていました。
で、シートのコピーを実行すると「Message」シートの後ろにコピーされているようでした。
（Visible=Trueが「Message」シートだけなので当たり前なのかな？）
ScreenUpdating = False　だったので気がつきませんでした。
なので、「Message」シートを表示する時にすべてのシートを表示してから
シートのコピー→名前の変更をしたら上手くいきました。
みなさんのコードを見てるともっと良いやり方もありそうですが、時間が無いのでこれで行こうと思っています。