Excelユーザーフォームでシートの移動

質問

Excel2007です。

データを集計するためのデータブックがあり、その中に30ほどのシートがあります。
このデータ集計を別に作成したマクロブックから操作しています。
データブックは毎日その日ごとのものが作成されます。
30ほどのシートの中で、その日の集計が終わったシートを選択して、別のシート移動用ファイルに移動させるようにしたいと思い、ユーザーフォームを作成しました。
ユーザーフォームにリストボックスを置き、そこにデータブックにあるシートの一覧が表示されます。
その中で選択したシートを「移動」というコマンドボタンで指定したファイル（この場合はBool2.xlsx）に移動させようとしています。

下記のコードを書いたところ、ユーザーフォームを開いて一度移動の操作でBook2に移動するまではできたのですが、更に別のシートを移動しようとすると
「インデックスが有効範囲にありません」
のエラーが出ます。
ユーザーフォームを一旦閉じて再度開くとエラーなく移動処理できます。
また、移動処理後にリストを再取得する方法がわからなくて、既に移動したシートもリストボックスに表示されたままになります。

ユーザーフォームを閉じることなく続けて移動処理をしたいのと、移動させたものをリストボックスから削除するにはどうしたらよいでしょうか。

’リストボックスにシート一覧を表示

Private Sub UserForm_Initialize()
Dim a As Worksheet

ListBox1.MultiSelect = 2

For Each a In Sheets
ListBox1.AddItem a.Name
Next

End Sub

’Book2にシートを移動

Private Sub SheetIdou_Click()

Dim i As Integer
Dim v() As String
Dim k As Integer

With ListBox1
ReDim v(1 To .ListCount)
For i = 0 To .ListCount - 1
If .Selected(i) Then
k = k + 1
v(k) = .List(i)
End If
Next i

If k = .ListCount Then
MsgBox "すべてのシートを移動することはできません"
Exit Sub
End If

End With

ReDim Preserve v(1 To k)

If MsgBox("以下のシートを移動しますか？" & vbLf & Join(v, "/"), vbYesNo, "確認") = vbYes Then
Sheets(v).Move After:=Workbooks("Book2.xlsx").Worksheets(1)

End If

End Sub

よろしくお願いいたします<(__)>

回答No.3

keithin · ベストアンサー

ブックが毎回変わることは元のご相談から読み取れましたが，少なくとも「今日の対象のブックを開く」ことだけは出来ていたからこのマクロが動いたので，出来ると思いましたが無理ですか。

では仕方ないのでお薦めしたくない手段ですが，少なくとも「リストボックスから選んで『これとこれのシートを移動しろ』と命じるその瞬間」には，データブックがアクティブになっているハズだという前提で綱渡りをしましょうか。

UserForm_Initializeは，以前に他の誰かが作ってくれたマクロ？のその通りの状態に正しく戻します。
絶対に直し間違えないように，よくよく注意してください。

Private Sub SheetIdou_Click()
　dim Targetbook as workbook

’（中略）
　If MsgBox("以下のシートを移動しますか？" & vbLf & Join(v, "/"), vbYesNo, "確認") = vbYes Then

　　set targetbook = activeworkbook
　　Sheets(v).Move After:=Workbooks("Book2.xlsx").Worksheets(1)
　　targetbook.activate
　　listbox1.clear
　　userform_initialize
　End If

End sub

＃今となっては別に構わないことですが
＞とりあえずアクティブなシートを対象にするのは当たり前ですよね

いいえ，全然逆です。
アクティブなブック，アクティブなシートを対象にするのは，まさに今回ひっかかったように失敗しやすい「典型的なダメなマクロ」です。

お礼

ありがとうございます。

ダメなマクロ…その通りです…
ダメなのは分かりつつもどこをどうしたらダメじゃなくなるのかが分かっていないという…

今回のユーザーフォームは完全に自分で作ったのでUserForm_Initializeも自分で作りました。
他の（詳しい人が作った）ユーザーフォームは少し特殊な操作をするコードが書いてあるのでそのままあてはめることはできませんでした。

なのでUserForm_Initialize自体をなくして、コマンドボタンをもう一つ作ってそこでシート一覧のリストを取得するようにしました。

何度もご回答ありがとうございました。

回答No.2

keithin

簡易には
１．sheets(v).moveをした直後に
２．リストボックスをclearして
３．userformのinitializeをやり直す
でいいと思います。

ただし一つ問題なのは，今のUserForm_Initializeのプロシジャが「作業対象のデータブックを指定せずに」アクティブブックを対象としている中途半端なマクロになっているため，今のまま動かすと上手く作動しません。

作成の基本方針：
Private Sub UserForm_Initialize()
’(前略）

’★下記のままでは上手くないので，キチンと作業対象ブックを取得して指定すること
　For Each a In workbooks("データブック.xlsx").Sheets　
　ListBox1.AddItem a.Name
　Next
End Sub

Private Sub SheetIdou_Click()
’（前略）
　If MsgBox("以下のシートを移動しますか？" & vbLf & Join(v, "/"), vbYesNo, "確認") = vbYes Then
　　Sheets(v).Move After:=Workbooks("Book2.xlsx").Worksheets(1)

　　listbox1.clear
　　userform_initialize
　End If

End sub

補足

ありがとうございます。

UserForm_Initializeプロシジャが中途半端なのには理由がありまして…

このマクロブックは複数のデータブックに使うものなのですが、そのデータブックの名前が毎日変わるのです。
（格納されているフォルダも日毎に変わる）
それで、他の操作をするマクロでは常にそのデータブックをアクティブにしておくコードを詳しい人に書いていただいたのですが、私があまり知識がないものでその指定方法が分からず…
（書かれたコードを見ながら応用できないかと試行錯誤しましたができませんでした）

よく考えたらおっしゃる通り、とりあえずアクティブなシートを対象にするのは当たり前ですよね
再取得する時にデータブックを指定することは無理でしょうか…

回答No.1

mt2008

詳しく見ていませんが、シート移動後に
Call UserForm_Initialize　と、でも入れてリストを更新してやっては？

お礼

ありがとうございます。

他の問題もあって指定したファイルの一覧が再取得できません…