こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

ExcelのVBAでシートのコピー

Excel2000
Win2000

複数(最低5)のシートが存在するExcelのBookでExcelのVBAを使用して

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

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

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

投稿日時 - 2005-05-03 16:41:10

QNo.1365885

すぐに回答ほしいです

質問者が選んだベストアンサー

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」シートだけなので当たり前なのかな?)

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

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

投稿日時 - 2005-05-05 00:26:23

補足

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

投稿日時 - 2005-05-05 13:17:18

ANo.9

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(10)

ANo.10

おはようございます。

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

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

投稿日時 - 2005-05-05 11:12:34

補足

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

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

投稿日時 - 2005-05-05 13:24:40

ANo.8

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

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

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

投稿日時 - 2005-05-04 23:22:41

ANo.7

こんばんは。

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は必ずしも同じではないということだけですよね。

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

投稿日時 - 2005-05-04 22:08:33

補足

えぇ~と、私はそこの所は
シート名が「Sheet1」だからといってインデックスも「1」とは限りませんよ
と受け取りました。

投稿日時 - 2005-05-04 22:44:41

ANo.6

#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 とは、同時に扱えないわけです。

投稿日時 - 2005-05-04 10:49:41

補足

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

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

状況

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の2~5が非表示(Visible=False)となっており
コードが実行されると「Message」シートが表示され他のシートはすべて非表示と
なっていました。
で、シートのコピーを実行すると「Message」シートの後ろにコピーされているようでした。
(Visible=Trueが「Message」シートだけなので当たり前なのかな?)
ScreenUpdating = False だったので気がつきませんでした。
なので、「Message」シートを表示する時にすべてのシートを表示してから
シートのコピー→名前の変更をしたら上手くいきました。
みなさんのコードを見てるともっと良いやり方もありそうですが、時間が無いのでこれで行こうと思っています。

投稿日時 - 2005-05-04 21:28:50

ANo.5

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

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

投稿日時 - 2005-05-04 01:20:27

補足

Excelシートは

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

と理解しております。

投稿日時 - 2005-05-04 22:03:20

ANo.4

>コピーされたシートのインデックスが2になると思っていたので上手く行きません。
私も、以前混同していたことがあります。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

投稿日時 - 2005-05-03 22:23:16

補足

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

投稿日時 - 2005-05-04 21:48:20

ANo.3

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

で、どうなるんですか?

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

投稿日時 - 2005-05-03 22:15:36

ANo.2

Set ns = Worksheets.Add

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

で、どうでしょうか。

投稿日時 - 2005-05-03 20:40:31

ANo.1

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

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

投稿日時 - 2005-05-03 17:00:32

補足

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

投稿日時 - 2005-05-04 21:43:46

あなたにオススメの質問