Office TANAKA - VBAでテーブルの操作[特定のデータだけコピーする]

実務で最もやる機会が多いであろう、テーブル内の特定データをコピーするやり方を解説します。ここでは、下図のテーブルから、[名前]が"田中"であるデータだけを、別のシートにコピーしてみます。なお、コピー元のテーブルはアクティブシートにあり、コピー先はSheet2とします。

タイトル行ごとコピーする

Sub Sample1()
    With Range("A1").ListObject.Range
        .AutoFilter 2, "田中"
        .Copy Sheets("Sheet2").Range("A1")
        .AutoFilter 2
    End With
End Sub

【実行結果】

タイトル行を除いて実データだけコピーする

Sub Sample2()
    With Range("A1").ListObject.DataBodyRange
        .AutoFilter 2, "田中"
        .Copy Sheets("Sheet2").Range("A1")
        .AutoFilter 2
    End With
End Sub

【実行結果】

コピー先で、[日付]列の列幅は自動調整されません。コピー元の[日付]列の列幅を広げておいたので、コピー先も私が手動で列幅を調整しました。

いずれにしても、テーブルに設定されている縞々の書式が一緒にコピーされます。この書式、いらいないときもあります。むしろ、よくあります。特に実データだけ、別の表に集約するような場合、まさに"余計なお世話"です。縞々の書式をつけないでコピーすることはできませんから、ここは苦肉の策です。コピーする前に、テーブルをスッピンの"書式なし"にしてからコピーし、コピーが終わったら元の書式に戻してやります。テーブルに設定されている書式は、TableStyleプロパティで分かります。TableStyleプロパティには、設定する書式の名前を文字列で指定します。TableStyleプロパティに空欄("")を指定すると、書式は"なし"になります。詳しくは「セル範囲をテーブルにする」をご覧ください。

Sub Sample3()
    Dim A As String
    With Range("A1").ListObject
        A = .TableStyle
        .TableStyle = ""
        With Range("A1").ListObject.DataBodyRange
            .AutoFilter 2, "田中"
            .Copy Sheets("Sheet2").Range("A1")
            .AutoFilter 2
        End With
        .TableStyle = A
    End With
End Sub

【実行結果】

特定の列だけコピーする

テーブル全体ではなく、特定の列だけコピーするには、テーブルのListColumnをコピーしてやります。

Sub Sample4()
''タイトル行ごとコピーするとき
    With Range("A1").ListObject
        .Range.AutoFilter 2, "田中"
        .ListColumns(2).Range.Copy Sheets("Sheet2").Range("A1")
        .ListColumns(4).Range.Copy Sheets("Sheet2").Range("B1")
        .Range.AutoFilter 2
    End With
End Sub

Sub Sample5()
''実データだけコピーするとき
    With Range("A1").ListObject
        .Range.AutoFilter 2, "田中"
        .ListColumns(2).DataBodyRange.Copy Sheets("Sheet2").Range("A1")
        .ListColumns(4).DataBodyRange.Copy Sheets("Sheet2").Range("B1")
        .Range.AutoFilter 2
    End With
End Sub

テーブルのバグ

テーブルのコピーでは、変な現象が起こります。なぜそうなるのか、そうすべきなのか、合理的な理由が思いつきませんので、これはバグでしょう。しかも、けっこう致命的なバグです。Microsoftも、そのうち修正するでしょう。まずは、その変な現象をご覧ください。

上記の状態で、先の

Sub Sample1()
    With Range("A1").ListObject.Range
        .AutoFilter 2, "田中"
        .Copy Sheets("Sheet2").Range("A1")
        .AutoFilter 2
    End With
End Sub

を実行します。実行結果は下図のようになります。

では、同じコードを次の状態で実行します。

実行結果は下図のようになります。

オートフィルタで絞り込んだ結果だけでなく、すべてのデータがコピーされてしまいました。さらにコピー先もテーブルになります。違いが分かりますか？実行前の状態を、よ～く見てください。正解はCMの後で…

オートフィルタで絞り込んだ結果だけが正しくコピーされたときは、アクティブセルがテーブル内にありました。対して、オートフィルタの結果が無視されて、すべてのデータがコピーされてしまったときは、アクティブセルがテーブルの外にありました。その違いです。これを発見したのは、本当に偶然です。マクロを実行するとき、アクティブセルの位置によって動作が異なるなんて、今までのExcelで私には記憶がありません(ホントは、いくつかありますけど、どれも理由が分かるものばかりです)。ちなみにこれ、DataBodyRangeだったらうまくいくんです。ますますもって、そうする理由が思いつきません。

じゃ、今までワークシート上でやってきた方法でコピーしてみましょう。通常のワークシート上では、テーブルのRangeとかDataBodyRangeみたいなのはありませんから、CurrentRegionを使いました。

Sub Sample6()
    With Range("A1")
        .AutoFilter 2, "田中"
        .CurrentRegion.Copy Sheets("Sheet2").Range("A1")
        .AutoFilter 2
    End With
End Sub

結果は同じです。CurrentRegionで「タイトル行を含む全データ」をコピーするとき、アクティブセルがテーブルの外にあると、絞り込んだ結果だけでなく、全データがコピーされてしまいます。いや、待ってください。もしかしたら、今までもそうだったのでしょうか？通常のワークシート上で、オートフィルタの結果をコピーするときも、アクティブセルによる動作の違いがあったのでしょうか。テーブルを通常のセル範囲に変換して、上記のマクロを実行してみます。結果はどうなるでしょうか！詳しくはWebで！(あ、ここか…)。

テーブルではなく、通常のセル範囲で試したところ、上記のようにCurrentRegionを使うと、アクティブセルが表の中にあろうと外にあろうと、どちらも正常に絞り込まれた結果だけがコピーされます。変な現象が起こるのは、テーブルのときだけです。ここからは推測です。

CurrentRegionというのは、手動操作で「アクティブセルを含むひとかたまりのセル範囲を選択」する Ctrl + Shft + * と同じです。このとき、基準となるのは必ずアクティブセルです。手動操作だったら、それしかできませんから
そのCurrentRegionをVBAから実行するときは、基準となるセルを自由に指定できます。Range("B4").CurrentRegion みたいに。VBAからこれを実行すると、Excelは内部で(アクティブセルではなく)指定されたセルを含む「ひとかたまりのセル範囲」を返します
ListObjectのRangeは、まさにこのCurrentRegionです。おそらく内部でも同じ仕組みを使っているのでしょう。しかしそれは「テーブルのひとかたまりのセル範囲」でなければいけません。しかし上記のように、手動操作の「アクティブセルを含む」という動作と、VBAからの「指定されたセルを含む」という2つの動作があるのですから、ここは後者の考えを使わなければならないところ
テーブルのクラスを設計するとき、うっかりして前者の「アクティブセルを含む」の方を使っているのではないかと。だからいくらテーブルを指定しても、アクティブセルの位置によって正しいセル範囲が返されないのではないかと

さて、ではどうするかと。そもそも、オートフィルタで絞り込まれた結果を普通にコピーして、非表示のセルを除いて表示されているセルだけがコピーされるのは、Excelの親切機能です。Excel 2000までは違いました。Excel 2002で(何のアナウンスもなく)変わったんです。Excel 2000までは、ひとかたまりのセル範囲をコピーすると、非表示のセルも一緒にコピーされてしまいました。何度心の中で「使えねぇ～」とつぶやいたことでしょう。そこでExcel 2000までは、必ず可視セルを指定していました。

Sub Sample6()
    With Range("A1")
        .AutoFilter 2, "田中"
        .CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1")
        .AutoFilter 2
    End With
End Sub

今回のテーブルには、この手が通用するのでしょうか。

Sub Sample7()
    With Range("A1").ListObject.Range
        .AutoFilter 2, "田中"
        .SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1")
        .AutoFilter 2
    End With
End Sub

うまくいきました。SpecialCellsを絡めることで、アクティブセルがテーブルの外にあっても、正しく絞り込まれた結果だけがコピーされました。当面は、この手で乗り切るしかありませんね。

冒頭で"致命的なバグ"といったのは、アクティブシートではない、別のシート上にあるテーブルをオートフィルタで絞り込み、その結果を別のシートにコピーするケースです。これ、実務ではよくあります。よろしいですか、アクティブシートではないシート上のテーブルを操作するということは、絶対にアクティブセルはテーブルの中に存在しません。だって、アクティブセルはアクティブシートにしかないのですから。したがって、アクティブシートではないシート上のテーブルからコピーするときは、必ず全データがコピーされちゃうってことです。あり得ないです…。まだ、VBAでテーブルを操作する人は少ないでしょうから、今のうちにコッソリ、ProPlusの月次チャネル更新で直して欲しいです。ああ、もちろん、直したときはちゃんと告知して欲しいです。ある日急に、ってのはかんべんしてください。