オートフィルターで抽出した結果を写すマクロが動かないが?

Question 15.2 Previous Next
はじめまして。いつも、あなた様のホームページを利用させていただき、マクロ作成に努力しています。なかなか、初心者の域を脱せず悪戦苦闘しております。
オフィス95から97に変更したところ、今までのマクロが作動しなくなり、困っています。よいお知恵をお借りできないでしょうか?
<内容>
Sub オートフィルターで抽出した結果を写す_質問()     '1
  元シート = "抽出シート"                        '2
    条件1 = "2**"                               '3
    条件2 = "2**"                               '4
    論理式 = "and"                              '5
    キー位置 = "a1"                             '6(回答の都合で質問ではp1をa1に変更)
    新シート = "バイキング取込みシート"           '7
    貼付位置 = "a1"                             '8
    Sheets(元シート).Select                     '9
        Range(キー位置).Select                  '10
        Selection.AutoFilter                    '11
        Selection.AutoFilter Field:=2, Criteria1:="2**" '12
        Selection.CurrentRegion.Copy            '13
    Sheets(新シート).Select                      '14
        Range(貼付位置).PasteSpecial Paste:=xlAll '15
        Selection.CurrentRegion.Select          '16
End Sub                                         '17
14行目の「Sheets(新シート).Select」の部分で、実行時エラー 9が発生してインデックスが有効範囲にありません。とメッセージが出ます。誠に不躾と思いますが、何卒、よろしくお願い申し上げます。
Answer   Copyright (C) 1999.2.6 永井善王
「Macro」の「コピー」のページに掲載されている「オートフィルターーしてコピー」を、ご活用いただいているのですね。
うまく動かないとのことですので、あなたと同様の環境を作り、あなたのマクロを使って再現テストをしてみました。
ただし、あなたのシートはオートフィルターするキーがP列にある大きなシートですが、ここでは下図のように必要最小限のサイズにしました。
A図 抽出シート
    (オートフィルター実行前)
a
B図 抽出シート
    (オートフィルター実行後)
a
C図 バイキング取込みシート
    (貼り付け後)
a
あなたのマクロの概要は、
1) A図のB(p)列の分類コードが「2**」のデータをオートフィルターで抽出する
2) 抽出結果(B図)を、そっくりコピーする
3) バイキング取込みシート(C図)に、すべて貼り付ける という内容ですね。

再現テストの結果としては、Excel95と97の両方とも、異常なく動作しました。
本来は、あなたのパソコンで「実行時エラー」になる原因を究明して対応すべきところですが、エラーが再現しなかったので、あなたのマクロを下記のとおり整理し、補足説明を加えておきました。
もし良ければ、これを使って動かしてみてください。
Sub オートフィルターで抽出した結果を写す_参考()     '1
  元シート = "抽出シート"                        '2 抽出するシート名を指定
    条件1 = "2**"                               '3 抽出条件を指定
    キー位置 = "b1"                              '6 (p1をb1に変えました)
    キー列 = 2                                  '18 抽出キーの列を指定
    新シート = "バイキング取込みシート"           '7 貼り付けするシート名を指定
    貼付位置 = "a1"                             '8 貼り付け位置を指定
    Sheets(元シート).Select                     '9 抽出するシートを選択する
        Range(キー位置).Select                  '10 キー位置(B1)セルを選択する
        Selection.AutoFilter                    '11 オートフィルターをリセットする
        Selection.AutoFilter Field:=キー列, Criteria1:=条件1
                                          '12 キー列(B)が 条件1(2**)のデータを抽出
        Selection.CurrentRegion.Copy            '13 抽出された範囲をコピーする
    Sheets(新シート).Select                      '14 貼り付けるシートを選択する
        Range(貼付位置).PasteSpecial Paste:=xlAll '15 貼付位置を左上角としてすべて貼付
        Range(貼付位置).Select                  '19 貼付位置(A1)セルを選択する
        Selection.CurrentRegion.Select          '16 貼り付けられた範囲を選択する
End Sub                                         '17
改良カ所を説明します。
18行目を追加 … 抽出キーの列名(2=B)を変数「キー列」に設定する、 19行目を追加 … 16行目の範囲選択の準備、
4~5行目を削除 … 使ってないので、 12行目を変数名に変更 … 3,18行で変数「条件1、キー列」にセットされた値で抽出する。
6行目は「p1」にもどしてください。

この参考マクロを、あなたのモジュールシートへ写す方法がわからなければ、Introductionのモジュールシートへ写し取る方法の 4. 以後を参考にしてください。

サンプルブックのダウンロードは ここをクリック  (YNxv99_autof_95.xls 19KB)
※ 一旦、ブックをハードディスクに保存し、後で改めて開いてから実行してください。

Excel VBA Macro