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

Question

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

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

keithin · Accepted Answer

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

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

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

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

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

keithin · Answer

簡易には
１．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

mt2008 · Answer

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

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

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

簡易には

この回答への補足

詳しく見ていませんが、シート移動後に

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング