VBAで実行時エラーとコピーの仕方

質問

始めまして、宜しくお願い致します。拡張子がxlsmの中に名前のついた10個のsheetがあります。この10個のsheetを拡張子がxlsxのBookにコピーしたいのですが、実行時エラーが出ます。作成したマクロは
Option Explicit
Option Base 1
Public Sub シートの纏め()
Dim i As Long
Dim mySheetCnt As Long
Dim mySheetName() As String
Dim ws As Workbook
Dim s As Worksheet
'==========================================================================
mySheetCnt = ThisWorkbook.Sheets.count
ReDim mySheetName(1 To mySheetCnt)
For i = 1 To mySheetCnt - 3
mySheetName(i) = Sheets(i).Name 'ここで実行時エラーが出ます。
'MsgBox "変数mySheetName(" & i & ")=" & mySheetName(i)
Next i
'==========================================================================
Dim EffectiveRow As Long
Dim EffectiveColumn As Long
EffectiveRow = Range("B65536").End(xlUp).Row
'MsgBox "EffectiveRow = " & EffectiveRow & ""
EffectiveColumn = Cells(4, 256).End(xlToLeft).Column
'MsgBox "EffectiveColumn = " & EffectiveColumn & ""
'==========================================================================
'MsgBox "デフォルトで" & Application.SheetsInNewWorkbook & "枚作成されます"
Application.SheetsInNewWorkbook = 1
Workbooks.Add
'==========================================================================
For i = 1 To mySheetCnt - 3
If mySheetCnt = 11 Then GoTo Label1
MsgBox "mySheetName(i) = " & mySheetName(i) & ""
Workbooks("Book1.xls").Worksheets("sheet1").Range("A4").Select
Next i
Label1:
End Sub
です。ここでコメント欄にエラーと書かれた部分で実行時エラーが起こります。エラーメッセイジは、
実行時エラー「'9'インデクスが有効範囲にありません。」と出ます。また、拡張子がxlsmにある10個名前のついたsheetを拡張子xlsxを作り、1つのsheetに纏めたいのですが、方法が判りません。纏め方は、10個名前のついたsheetから、新たに作ったsheetに下から上に順番にコピーしたいのですが、方法がわかりませ。どなたかご教授して頂きたく宜しくお願い申し上げます。実行環境はWindowsXPSP3でEXCELは2010を使っております。

回答No.2

queuerev2 · ベストアンサー

試しているうちに一応エラーを再現できました。
環境はWindows XP SP3, Excel 2007で、マクロは標準モジュールに入れました。

マクロ実行時に他のブックがアクティブになっていて Sheets(i).Nameでそのブックを見に行っている可能性があります。

対策ですが、
mySheetName(i) = Sheets(i).Name 'ここで実行時エラーが出ます。
を
mySheetName(i) = ThisWorkbook.Sheets(i).Name
に変更したところ、この行があるFor Nextループが完了するところまでは行きました。
（その後は試していませんが・・・）

お礼

ご回答頂き本当ｊに有難う御座います。しかし、別の方法で解決させて頂きました。何卒今後ｔも宜しくお願い申し上げます。

回答No.1

bin-chan

空シートにデータをコピーする「シートのコピー」もいいけど、
シートそのものをブック間でコピーor移動させてはどうですか？

「マクロの記録」でシートそのものをブック間でコピーor移動させて、
ＶＢＡのコードでどう記述すれば良いかを確認できますよ。