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.9

Wendy02 · ベストアンサー

INOPIIさん、StudyVBAさんへ

もう、INOPIIさんも、StudyVBAさんは、お分かりにはなっているはずですが、一応、念のために、レスをつけておきます。
＞質問者のコードが引用されていますので、そこだけ読むと質問者のコードではダメ
＞ですよ、
＞というふうに取れ、No.4の回答と矛盾するようにかんじるのですが、

私は、INOPIIさんの着目している点は、私が以前指摘されたこともあって、直感的に分かりました。INOPIIさんの状況も正しければ、他の方が、Index が 2 になることも正しいと思いましたが、他の方のご指摘になっているものと、INOPIIさんの条件が違うということですね。

これは、最初の　#1 のBLUEPIXY　さんの回答をずっと追いかけていたのです。
# SheetsのIndex は、Index でしかありません。示されたコードで、何の問題もありません。

ただし、そのシート名とIndex は、つながらないっていう話を#4 で書いて、#6 で実証しようとしたのです。

StudyVBAさんもお分かりになっているとおりなんです。
＞シート名が「Sheet1」だからといってインデックスも「1」とは限りませんよ
＞と受け取りました。

今回、私自身も新たな発見をしました。

＞シートのコピーを実行すると「Message」シートの後ろにコピーされているようでした。（Visible=Trueが「Message」シートだけなので当たり前なのかな？）

私は、考えても見ませんでした。非表示は、まず、一番最後にまわすのと、最終段階のブックでつけるので、そういうことはやったことがないのです。たぶん、私も新たにコードは考えられるかもしれませんが、もう、分かっていらっしゃるようですね。

つたない私の説明で、お分かりになりにくかったかもしれませんね。

補足

まだ、何故そうなってるのか分かっていないので、
これから調べる所です。
ただ、ここはもう閉じようと思います。
非表示にしたのは、多分（PGさんが作ったのを連休を使ってテストしていたので・・・）シートが色々切り替わって目障りなのでそうしたんだと思います。

回答No.10

StudyVBA

おはようございます。

Wendy02さん、わざわざの回答ありがとうございます。
当方の読解力不足でご迷惑をお掛けしました。
これからも惚けた質問をすると思いますが
今回同様、宜しくお願いいたします。

それにしても本来のINOPIIさんの質問ですが、
シートが非表示になってるとは思いもよりませんでした。
色々勉強になることばかりです。

補足

私は構わないと思います。
もし、後で見る事があった時に疑問があっても、もう聞けないですから。
言葉だけでやってるので、伝わらない所がでるのはしょうがないですし。
画像がUPできると楽になる部分も多いんですけどね。

まぁ、他の人は分からないんで、難しいですが（笑）

回答No.8

StudyVBA

INOPIIさん、こんばんは。
補足読みました。
そうですよね。

それから、質問者の了解を取らずに便乗質問したことお許しください。
先ず、断りを入れてから質問するべきでした。
以後気をつけます。

Wendy02さん、アホウな質問、すみませんでした。

回答No.7

StudyVBA

こんばんは。

Wendy02さん、またまた疑問符ありです。ご教授ください。

No.4 の回答で
＞示されたコードで、何の問題もありません。
と書いておられるのに

No.6 の回答では

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

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

と書いておられますが、質問者のコードが引用されていますので、そこだけ読むと質問者のコードではダメですよ、
というふうに取れ、No.4の回答と矛盾するようにかんじるのですが、
No.6で仰りたいのは、シート名とシートのindexは必ずしも同じではないということだけですよね。

宜しくお願いいたします。

補足

えぇ～と、私はそこの所は
シート名が「Sheet1」だからといってインデックスも「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」シートを表示する時にすべてのシートを表示してから
シートのコピー→名前の変更をしたら上手くいきました。
みなさんのコードを見てるともっと良いやり方もありそうですが、時間が無いのでこれで行こうと思っています。

回答No.5

kikaida-

＞この時コピーされたシートのインデックスが２にならないんですが、何故なんでしょ？
そらそうでしょう。すでに何枚かシートがあるんですよね。
シートのインデックスは視覚的に並んでいる順番ではなく、作成された順番です。
従って下記のようにすればよいかと思います。

Sheets(Sheets.Count).Name = "XXX"

補足

Excelシートは

インデックス：見た目の順番
オブジェクト名：作成された順番

と理解しております。

回答No.4

Wendy02

＞コピーされたシートのインデックスが２になると思っていたので上手く行きません。
私も、以前混同していたことがあります。SheetsのIndex は、Index でしかありません。示されたコードで、何の問題もありません。

しかし、それは、あくまでも　VBProjects 上のObject名とIndex の相関性があるだけであって、シートのプロパティ名とは直接関係がありません。

お出しになったコードを元に、試しに、以下のようなコードを書いてみました。

Sub SheetCopyW()
　Dim num As Variant
　Dim MaxNum As Integer
　Dim i As Long
　Const strSheetName As String = "Sheet1"
　On Error GoTo ErrHandler
　For i = 1 To Sheets.Count
　　With Sheets(i)
　　　If .Name Like strSheetName & "-*" Then
　　　　num = Mid(.Name, InStr(.Name, "-") + 1)
　　　　If Val(num) > MaxNum Then
　　　　　MaxNum = Val(num)
　　　　End If
　　　End If
　　End With
　Next
　If MaxNum > 0 Then
　　Sheets(strSheetName).Copy After:=Sheets(strSheetName & "-" & CStr(MaxNum))
　　Else
　　Sheets(strSheetName).Copy After:=Sheets(strSheetName)
　End If
　ActiveSheet.Name = strSheetName & "-" & CStr(MaxNum + 1)
　On Error GoTo 0
　Exit Sub
ErrHandler:
　MsgBox "Error " & Err.Number & " (" & Err.Description & ") "
End Sub

補足

コードありがとうございます。
テストしてみたんですが、Sheet1が非表示の時に
１回目は上手くいくんですが、２回目から上手く行きませんでした。
なぜ上手く行かないのかも良くわかりませんでした。
結果と詳しい事はNo6さんの補足に乗せさせてもらいました。

回答No.3

quit123

＞この時コピーされたシートのインデックスが２にならないんですが

で、どうなるんですか？

それを書かないと、No.1さんも回答されてるとおり
提示されたコードであれば上手く行くはずですから
この質問を読んだ人にとってはどんな状況なのか
見当もつきませんよね。

回答No.2

yos40

Set ns = Worksheets.Add

ns.Name = ws_edt.Range("comment1")

で、どうでしょうか。

回答No.1

BLUEPIXY

ExcelXP(2002)
WinXPPro
では、質問文のコードでうまく実行できました。

シートNo１のシートの後に挿入はされているんですよね。
コピーされたシートのインデックスはいくつになっているんでしょうか？
イミディエイトウィンドウで
for i=1 to sheets.Count : ? i;":";sheets(i).name : next
とかやってみたらどうなりますか？

補足

なぜだかよく分かっていないんですが、
上手く行くBookと行かないBookがあるんです。
なので、割りつくIndex番号もばらばらです。
詳しくはNo.6さんの補足に書かせていただきました。