Excel VBAで表組みしたらデバック発生

質問

Excel VBAの初心者です。Windows Vistaで
Excel2007を使っています。

表をマクロの実行で作成したいと思っています。

何もないエクセルブックより

「開発」→「マクロの記録」→「相対参照」
→「表の作成」→「記録終了」→「相対参照で記録の解除」
→「エクセルマクロ有効ブックで保存」

ところがこのマクロ記録が入ったブックを再度立ち上げ、
表をオールクリアにし、マクロボタンより表作成を実行
させようとすると、次のエラーメッセージがでました。

『実行時エラー'9'
インデックスが有効範囲にありません。』

デバックからModule1をみると以下の記述となっていました。

Sub 表組み()
'
' 表組み Macro
'

'
ActiveCell.Range("A1:E5").Select
Selection.Copy
Windows("Book1").Activate
ActiveSheet.Paste
ActiveCell.Columns("A:A").EntireColumn.Select
ActiveCell.Rows("1:1").EntireRow.RowHeight = 11.25
ActiveCell.Rows("1:5").EntireRow.Select
Selection.RowHeight = 21.75
ActiveCell.Columns("A:A").EntireColumn.ColumnWidth = 14.88
ActiveCell.Offset(0, 4).Range("A1").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.Offset(1, -3).Range("A1:D4").Select
Selection.NumberFormatLocal = "#,##0_ "
ActiveCell.Select
ActiveCell.FormulaR1C1 = "78000"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "102000"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "9800"
ActiveCell.Offset(-2, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "65000"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "204000"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "500"
ActiveCell.Offset(-2, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "86000"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "151000"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "10200"
ActiveCell.Offset(-2, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(0, -3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(-4, -2).Range("A1:D1").Select
Selection.AutoFilter
End Sub

上から9行目（？）のWindows("Book1").Activateに
黄色い矢印が示され、また行全体が黄色く四角に
覆われていました。

おそらくこの記述に問題があると思いますが、
どんな記述に変えたらいいのか分かりません。

Excel VBAにお詳しい方ご教示願います。
なお、マクロで作成したい図を添付いたします。
参考にしていただければ幸いです。

回答No.2

ap_2 · ベストアンサー

前回はエラー原因だけで、処理はちゃんと見てませんでした＾＾；

最前面のブックのA1～E5を選択
ActiveCell.Range("A1:E5").Select
選択範囲をコピー
Selection.Copy
別のブック"Book1"を最前面に開く
Windows("Book1").Activate
最前面のブックに貼り付け
ActiveSheet.Paste
以下、表の中の数字・計算式を入力する処理
…

＞補足
１＆２．上記の通り、２つのブック（Excelファイル）間で表をコピーして、表の中身だけ書き換えるマクロになっています。どうも期待する処理じゃなさそうですね…
３．オールクリアした＝コピー元が無いので、表の数値入力だけ行われているのかな。

記録機能は、あくまで同じ操作を繰り返すだけです。コピーをする場合、当然ながらコピー元のデータが必要です。ファイルを切り替える場合は、ファイル名が固定になってしまうので、改造が必要になります。
…ということで、添付図通りの表をボタンひとつで作りたいなら、「まっさらなシートに表を作る」過程を記録しないとダメです。作ってみました↓
※読み易いよう、記録に手を加えてあります。

Sub Macro1()
　'表の範囲選択。Selectionは選択範囲
　Range("A1:E5").Select
　'罫線を設定
　Selection.Borders(xlDiagonalDown).LineStyle = xlNone
　Selection.Borders(xlDiagonalUp).LineStyle = xlNone
　Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
　Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
　Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
　Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
　Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
　Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
　'書式設定
　Range("A1:E5").VerticalAlignment = xlCenter '縦位置
　Range("B1:E1").HorizontalAlignment = xlCenter '横位置
　Range("B2:E5").NumberFormatLocal = "#,##0_ " '数値書式
　Range("B1:E4").AutoFilter 'オートフィルタ
　Columns("A:A").ColumnWidth = 14.88 '幅
　Rows("1:5").RowHeight = 21.75 '高さ
　'表の中（Formulaは計算式）
　Range("A1") = "品名"
　Range("A2") = "乳製品"
　Range("A3") = "栄養ドリンク"
　Range("A4") = "清涼飲料"
　Range("A5") = "合計"
　Range("B1") = "営業一課"
　Range("C1") = "営業二課"
　Range("D1") = "営業三課"
　Range("E1") = "合計"
　Range("B5").Formula = "=SUM(B2:B4)"
　Range("C5").Formula = "=SUM(C2:C4)"
　Range("D5").Formula = "=SUM(D2:D4)"
　Range("E5").Formula = "=SUM(E2:E4)"
　Range("E2").Formula = "=SUM(B2:D2)"
　Range("E3").Formula = "=SUM(B3:D3)"
　Range("E4").Formula = "=SUM(B4:D4)"
　'値の入力も必要？
　Range("B2") = 78000
　Range("B3") = 102000
　Range("B4") = 9800
　Range("C2") = 65000
　Range("C3") = 204000
　Range("C4") = 500
　Range("D2") = 86000
　Range("D3") = 151000
　Range("D4") = 10200
End Sub

こんな感じですか？このまま使うワケじゃないでしょうし、補足いただければ直しますよ。

補足

たびたびのご回答ありがとうございます。

＞１＆２．上記の通り、２つのブック（Excelファイル）間で表をコピーして、
表の中身だけ書き換えるマクロになっています。どうも期待する処理じゃ
なさそうですね…

１＆２．ご指摘の通りです。
『10日で覚えるExcelVBA入門教室』という本の最初の方の
例題をやっているため、別のブックを立ち上げて
そこからでも同じマクロを実行する記述ではないようですね。
それはこれから勉強してできるようにします。

３．ご回答のコードをコピペしてご説明の部分を
削除したら、表ができました。また違うシートにも
マクロを実行することができました。ありがとうございます。

最後に一つ質問です。

コードをそのままコピーしたときご説明の日本語部分は
エディターの中で緑色になっていましたが、これは
削除した方がいいですよね？

三度にわたる質問ですが、よろしくお願いします。

回答No.3

ap_2

なるほど、勉強目的でしたか。１０日で覚える懐かしいですね、僕も立ち読みした記憶がありますｗ

「'」からその行の最後までは、コメント（緑色）になります。残しておいて構いません。複雑なマクロを作る場合は、コメントがあると何の処理かわかりやすいので、積極的に書き足していくといいですよ。

このさき、記録だけじゃなく、コードを自分で書くようにもなっていくと思います。慣れるまでは悩みも多いと思いますが、VBAはそれほど難しいモノじゃないので、がんばってください！
|_・）ノシ　またお会いしましょう

お礼

たびたびご丁寧なアドバイスありがとうございます。
毎日ちょっとずつ勉強して、早く表計算の自動処理が
できるようになりたいです。

回答No.1

ap_2

> インデックスが有効範囲にありません
データや、シートが見つからない時に発生するエラーです。

> 上から9行目（？）のWindows("Book1").Activateに
ということは、"Book1"が無いんじゃないかな？
セルをコピーし、Book1に切り替えて、貼り付けを行ってます。この処理が不要なら削除、必要ならブック名を直してください。

1操作毎に記録内容を見ることも、F8キーで1行ずつ実行することもできます。各行の意味を調べるのは難しくないと思うので、不要な部分は削除しちゃうといいですよ。

補足

早速のご回答ありがとうございます。

ご回答について3点質問事項があります。
まだはじめて4日目なのでap_2さんの
ご回答の内容をよく理解できず恐縮です。

１．「セルをコピーし、Book1に切り替えて、貼り付けを行ってます」
この作業は、単にエクセルを立ち上げて、「表をコピペする」
という意味でしょうか？

２．１で「表をコピペする」した後、シートの内容をオールクリアに
した後、マクロを実行することによって同一の表を作成するには
どうすれば良いでしょうか？

３．Module1のWindows("Book1").Activateを削除した後、
シートの内容をオールクリア、再度マクロを実行すると
フィルタ機能と表の数字の部分だけが残り、品目名や
課名、罫線は作成されませんでした（質問の画像を参照
してください）。これはなぜでしょうか？